Dynamic Linq

There are cases where you might need to use dynmaic linq in your Linq Queries, for instance filtering of items based on multiple where clause.

Assume you have list of following Items :


var listofmobiles = new List<Mobile>()

{ 

new Mobile(){ModelNo=1,MobileType= "Android"}

new Mobile(){ModelNo=2,MobileType= "Android"}

new Mobile(){ModelNo=3,MobileType= "BlackBerry"}

new Mobile(){ModelNo=4,MobileType= "Symbian"}

new Mobile(){ModelNo=5,MobileType= "IOS"

}

 }

public class Mobile

{

public long ModelNo {get;set;}

public string MobileType {get;set;}

}

We would write a simple linq query if we where suppose to query only one of mobiletype,assume a case where there can be more than one mobiletype(Android,Ios) the list should still return the items under Android and Ios.

Expression Trees :

To write Dynamic Linq queries,we must construct expression tress that matches the where clause.I am not going to explain more about expression tree but instead usage of System.Dynamic.Linq feature provided by framework which uses expression trees.

Using Dynamic Linq in where clause :

Given different mobiletype (Ios,Android,Symbain ) filter out the above list that matches the mobiletype.


var filterresult =listofmobiles.where("mobiletye @0 or mobiletye @1 or mobiletye @2","Android","IOS","Symbain").Tolist();

# Linq To Entities DBFunctions and SqlFunctions

When using linq to sql queries,few functions like DateTime Comparison,FirstorDefault would not work and you would end up getting exception “LinqtoEntities is not supported by this function”.

Problem:

For instance I was trying to compare a column of Date-time type with today’s date,I had issue when comparing it since the sql server Date-Time type was of (yyyy-mm-dd) and today’s date was of (mm-dd-yyyy),I was not able to use ToString method to format date since linq to entities  did not support it.

Solution:

LinqToEntities provides DBFunction and SqlFunction for sql related operations.It has got several methods like DateAdd,DatePart,TruncateTime etc…I had used the same to compare dates.


var somequery = from f in mytable where DbFunction.TruncateTime (f.mydatecolumn) == Dbfunction.TruncateTime(SqlFunction.GetDate())

 

 

Linq Query Recursive

This question is taken from Stack Overflow : )..I had taught to myself  writing recursive query with LINQ.

Question Link 

Input : 


public class InputClass
{
public int id { get; set; }
public string text { get; set; }
public string icon { get; set; }
public int? parentId { get; set; }

}

static void Main(string[] args)
{
var inputList = new List&lt;InputClass&gt;();
inputList.Add(new InputClass() { id = 1, text = &quot;Item #1&quot; });
inputList.Add(new InputClass() { id = 2, text = &quot;Item #2&quot; });
inputList.Add(new InputClass() { id = 3, text = &quot;Item #3&quot; });
inputList.Add(new InputClass() { id = 4, text = &quot;SubItem #1&quot;, parentId = 1 });
inputList.Add(new InputClass() { id = 5, text = &quot;SubItem #2&quot;, parentId = 1 });
inputList.Add(new InputClass() { id = 6, text = &quot;SubItem #3&quot;, parentId = 2 });
inputList.Add(new InputClass() { id = 7, text = &quot;Sub-Sub Item #1&quot;, parentId = 4 });

}

Output:

write a linq query to generate all child elements when parent id is passed.

Example :  when ParentId is 1 ,then the following output must be produced

ID  Name
4   Subitem #1
5   Subitem #2
7   Sub-Sub Item #1

Note : Id 4 & 5 has parent id of 1 and ID 7 has parent id of 4 which inturn points to parent id 1.

Code :

1.First we will write a method to return list of elements that matches the parent id.

Method :
 private static List&lt;InputClass&gt; GetAllChildElements(int parentid, List&lt;InputClas&gt;input)
 {
 return input.Where(x =&gt; x.parentId == parentid).ToList();
 } 
This method would return 4& 5 when parent id is passed as 1. 2.Next we must check if the returned list has any child elements associated with it. In this case returned list Id 4 has a reference to child element 7. we must again execute the same 
query but now the parent id would be returned list Ids.


 private static List<InputClass> GetRecursiveList(int p, List<InputClass> inputList)
 {
 var result = inputList.Where(x => x.parentId == p).ToList();
 var temp = new List<InputClass>();
 foreach (var inputClass in result)
 {
 temp.AddRange(GetRecursiveList(inputClass.id, inputList));
 }
 var t=result.Union(temp);
 return t.ToList();
 }