Skip to main content

Start querying with LINQ


  • Two fundamental building blocks of LINQ are the concepts of "elements" and "sequences"


Sequence



  • A sequence can be thought of as a list of items, with each item in the list being an element. A sequence is an instance of a class that implements the IEnumerable<T> interface.


    • Example for sequence


    int[] fibonacci = {0, 1, 1, 2, 3, 5};

  • sequence could be a local sequence of in-memory objects or a remote sequence

  • In the case of remote data sources (for example SQL Server), these remote sequences also implement the IQueryable<T> interface    


 


  • Queries that run on local sequences are known as local queries or LINQ-to-objects queries

     


  • Return value of query can be sequence or scalar value

     

     









int[] fibonacci = { 0, 1, 1, 2, 3, 5 };
// Scalar return value
int numberOfElements = fibonacci.Count();


Console.WriteLine("Count: {0}", numberOfElements);


 

// Output sequence return value


IEnumerable<int> distinctNumbers = fibonacci.Distinct();


Console.WriteLine("Elements in output sequence:");


 

foreach (var number in distinctNumbers)


{


Console.WriteLine(number);


}


 

Deferred execution


This means that the query does not execute when it is created, but when it is used or enumerated.

 

int[] fibonacci = { 0, 1, 1, 2, 3, 5 };


 

// Construct the query


IEnumerable<int> numbersGreaterThanTwoQuery = fibonacci.Where(x => x > 2);


 

// At this point the query has been created but not executed


 

// Change the first element of the input sequence


fibonacci[0] = 99;


 

// Cause the query to be executed (enumerated)


foreach (var number in numbersGreaterThanTwoQuery)


{


Console.WriteLine(number);


//query is executed when it's called


}


 

Result


99

3

5

 

Exceptional cases


  • operators such as Count will cause the query to be executed immediately, and not deferred

  • There are a number of conversion operators that also cause immediate query execution, such as ToList, ToArray, ToLookup, and ToDictionary.


 

Lambda expressions in query operators


fibonacci.Where(x => x > 2)


Here the lambda expression x => x > 2 will only return elements (ints in this case) that are greater than 2.

 

Local and interpreted queries


LINQ provides for two distinct architectures: local and interpreted.

Local queries operate on IEnumerable<T> sequences and are compiled into the resulting assembly at compile time. Local queries, as the name suggests, can be thought of as operating on sequences local to the machine on which the query is executing (for example, querying an in-memory list of objects).


Interpreted queries are interpreted at runtime and work on sequences that can come from a remote source such as an SQL Server database. Interpreted queries operate on IQueryable<T> sequences.


There are two styles of writing LINQ queries:





  • Fluent style (or fluent syntax)


  • Query expression style (or query syntax)



  1. Fluent Style (extension methods)


Fluent syntax makes use of the query operator extension methods as defined in the static System.Linq.Enumerable class

Query operators can be used singularly, or chained together to create more complex queries

  1. Query Expression


Query expressions offer a syntactical nicety on top of the fluent syntax.

SQL like querying

 

Example:

namespace ConsoleApp


{



class
Ingredient


{



public
string Name { get; set; }



public
int Calories { get; set; }


}


 


//Main Class



class
Program


{



static
void Main(string[] args)


{



Ingredient[] ingredients = {



new
Ingredient { Name = "Sugar", Calories = 500 },



new
Ingredient { Name = "Egg", Calories = 100 },



new
Ingredient { Name = "Milk", Calories = 150 },



new
Ingredient { Name = "Flour", Calories = 50 },



new
Ingredient { Name = "Butter", Calories = 200 }


};


 


#region Fluent style LINQ


 


IEnumerable<string> highCalorieIngredientNames =

ingredients.Where(x => x.Calories >= 150)


    .OrderBy(x => x.Name)


    .Select(x => x.Name);



//Ingredient object is transformed to a simple string. This transformation is called projection


 


//"x => x.Name" is called lambda expressions


 


foreach (var ingredientName in highCalorieIngredientNames) {



Console.WriteLine(ingredientName);


}


 

#endregion


 


#region Query expression style LINQ


 


IEnumerable<string> lowCalorieIngredientNames = from y in ingredients



where y.Calories <= 150



orderby y.Name



select y.Name;


 


foreach (var lowIngredient in lowCalorieIngredientNames) {



Console.WriteLine(lowIngredient);


}


 


#endregion


 


Console.ReadKey();


}


}


}

Range variables


 

  • Additional from clauses

  • The let clause


It is sometimes useful to store the result of a sub-expression in order to use it in subsequent clauses


 


IEnumerable<Ingredient> highCalDairyQuery =



from i in ingredients



let isDairy = i.Name == "Milk" || i.Name == "Butter"



where i.Calories >= 150 && isDairy



select i;



// isDairy keeps temporary value stored


 

 


//let can also be used to introduce a subsequence



string[] csvRecipes = { "milk,sugar,eggs",



"flour,BUTTER,eggs",



"vanilla,ChEEsE,oats" };


 


var dairyQuery = from csvRecipe in csvRecipes



let csvingredients = csvRecipe.Split(',')



from ingredient in csvingredients //multiple from clause



let uppercaseIngredient = ingredient.ToUpper()



where uppercaseIngredient == "MILK"


|| uppercaseIngredient == "BUTTER"


|| uppercaseIngredient == "CHEESE"



select uppercaseIngredient;


 


foreach (var dairyIngredient in dairyQuery) {



Console.WriteLine("{0} is dairy", dairyIngredient);


}


 

  • The into keyword


To be declared that can store the result of a select clause (as well as group and join clauses)


IEnumerable<Ingredient> highCalDairyQuery =



from i in ingredients



select
new
// anonymous type


{


OriginalIngredient = i,


IsDairy = i.Name == "Milk" || i.Name == "Butter",


IsHighCalorie = i.Calories >= 150


}



into temp



where temp.IsDairy && temp.IsHighCalorie



// cannot write "select i;" as into hides the previous range variable i



select temp.OriginalIngredient;



  • The join clause


The join clause takes two input sequences in which elements in either sequence do not necessarily have any direct relationship in the class domain model.

Common types of joins include:

 Inner joins.


 Group joins.


 Left outer joins.


Inner Join


//INNER JOIN



Recipe[] recipes = {



new
Recipe { Id = 1, Name = "Mashed Potato" },



new
Recipe { Id = 2, Name = "Crispy Duck" },



new
Recipe { Id = 3, Name = "Sachertorte" }};


 


Review[] reviews = {



new
Review { RecipeId = 1, ReviewText = "Tasty!" },



new
Review { RecipeId = 1, ReviewText = "Not nice :(" },



new
Review { RecipeId = 1, ReviewText = "Pretty good" },



new
Review { RecipeId = 2, ReviewText = "Too hard" },



new
Review { RecipeId = 2, ReviewText = "Loved it" }


};


 


var query = from recipe in recipes



join review in reviews



on recipe.Id equals review.RecipeId



select
new
// anonymous type


{


RecipeName = recipe.Name,


RecipeReview = review.ReviewText


};


 


foreach (var item in query) {



Console.WriteLine("{0} - '{1}'", item.RecipeName, item.RecipeReview);

}


Group join


//group join



var query = from recipe in recipes



join review in reviews



on recipe.Id equals review.RecipeId



into reviewGroup



select
new
// anonymous type


{


RecipeName = recipe.Name,


Reviews = reviewGroup // collection of related reviews


};


 


foreach (var item in query) {



Console.WriteLine("Reviews for {0}", item.RecipeName);


 


foreach (var review in item.Reviews) {



Console.WriteLine(" - {0}", review.ReviewText);


}

}


    


 




 




Comments

Popular posts from this blog

Turn off/ Hide Details panel/ Information Panel on modern SharePoint lists

Not always we require to show changes done by other which is a mandatory feature in SharePoint online. 
















What is details pane (aka. Information Pane)? Detail pane/ Information shows information regarding the document if you selected a one or its showing recent changes within a list or library.

Follow linkto Microsoft documentation about details pane.

Bad news: Until Microsoft listen to User Voice, there is no straightforward way to enable disable this even you don't want.
Good news: We could write a SharePoint framework extension to hack styles until Microsoft give us a permanent solution.

How? I found this sample project (Inject CSS into modern SharePoint pages with React) which could reuse to our purpose. Thanks to Hugo for saving my time.  Steps to awesomeness: Clone the projectResolve dependencies > npm iBundle > gulp bundle --shipPackage > gulp package-solution --shipUpload package into SharePoint App catalog and deploy. You could find package under SharePoint folder.A…

PowerApps Delegation warning

Warning:Delegation warning. This part "Filter" of this formula might not work correctly on large datasets. The data source might not be able to process the formula and might return an incomplete data set. Your application might not return correct results or behave correctly if the data set is incomplete
"Working with large datasets requires using data sources and formulas that can be delegated" - PowerApps 










Solution!!!500 is the default number of records to pull, but you can change this number for an entire app. 

By altering the number of delegation queries (not recommended), you will be able to pull items up to 2000 (as of 2018). 
On the File tab, select App settings.Find Experimental features, change the Data row limit for non-delegable queries setting from 1 to 2000.










Data sources that will help with delegation:Common Data Service

Microsoft Ignite Community (FREE) Events #MSIgnite

If you around Microsoft Ignite 2018, don't miss below community (FREE) events. 

Microsoft Ignite Welcome Reception

When: Monday, 5:30p – 7:30pm

Where: Exhibit Hall

Details: Kick off Ignite the right way with Monday’s welcome reception where you can chat with Microsoft experts, see demos, see old friends and make new ones over food and drinks.

PowerShell Community Social

When: Monday, 7:00p – 7:30p

Where: Community Central (OCCC West Hall)

Details: Come have a drink and meet Jeffrey Snover and PowerShell MVPs like Jaap Brasser, Aleksandar Nikolic, and Aaron Nelson immediately following the welcome reception.

Data & AI Community Social

When: Tuesday, 9:00a – 10:00a

Where: Community Central (OCCC West Hall)

Details: Do you do that Voodoo? Well, you can at the Data & AI Community Social. Eric Boyd and Rohan Kumar, along with MVPs Cathrine Wilhelmsen and Melody Zacharias will be there with Voodoo donuts and nitrogen cold brew coffee.

Microsoft Teams Community Social

When: Tuesday, …