Dynamic LINQ

Guess whose back? ...
Alright, I shall stop before I get sued for copyright infringements

I was at work today and the boss decided to throw me a challenge. Basically, he wanted to custom-build a SQL query but instead of hitting the database, obtain the data from cache.

I suggested LINQ but as soon as I said that, I had to swallow my words as to my knowledge, LINQ allow filtering but not dynamically.

I trolled the web and discovered that Scott, did blog about this 2 years back. Yes, I know I am 2 years late but better than never, right?

You can find it @ Dynamic LINQ - Part 1

I did a basic LINQ query and use the Where method extension but still to no avail but after some more poking around the web, a break through!

This is how you do it.

1. Download the samples from the Dynamic LINQ url (see above).
2. Extract the Dynamic.cs file from the DynamicLinq directory and include that into your project.
3. In the work-space that you would like to use this powerful class, simply declare the following

IQueryable linqVar = from someLinqVar in YourObjectList.AsQueryable()
               select someLinqVar;
var otherLinqVar = linqVar.Where("whr clause with @val", param object[] paramValsArrayObj);

Note: the "val" in @val is of integer type that ties into the object array, paramValsArrayObj.

An example LINQ-WHERE clause will be as follows :-
"Value = @0 && Text = @1"

4. You will also have to include the System.Linq.Dynamic library in the Using clause at the very top of the intended work-space.

Lambda expression

To implement a custom filtering or sorting of a LINQ query, simply implement LINQ lambda expression.


var mainLinqQuery = from someVar1 in someCollection select someVar1;


some while later after typing more lines of code.


var filteredLinqQuery = mainLinqQuery.Where(someVar2 => someVar2.someProperty == itemToFilterBy);


The result of the filtered LINQ query, filteredLinqQuery will be the specific result that we require.

Left Join revisited.

Remember my post on Friday, the 11th September 2009 about LINQ left joins?
Click here to navigate to the post


As I recently found out, should you attempt to left join 2 complex objects, you MUST instantiate the object of which will be null (the right sequence, object).

A better way to explain this is to provide an example.

C# code
-------
List employeeList = new List();
List companyList = new List();

var linqQuery = from eList in employeeList
join cList in companyList on
eList.CompanyId equals cList.Id
into joinEmpCompany
from jec in joinEmpCompany.DefaultIfEmpty(new Company())
select new
{
// insert whatever values you want here.
}

As you can see above, I instantiate a new Company object as there could be instances where the join will produce null results for the right-hand side (Company object).

Instantiating the Company object when using the DefaultIfEmpty function will allow LINQ to execute and not produce Null Reference errors.

Access to Modified Closure error

I was LINQ-ing today and had this obscure error that didn't have much meaning to until I trolled the tubes and came up with Franc Bouma's blog.

Franc's blog

I realized the error and would like to point out that LINQ doesn't like empty or null values in the WHERE clause. Be careful unless you like to get a generic LINQ error and go bonkers and waste countless hours scratching your head wondering what went wrong.

  245         private void DoNothing(List<SomeObject.SomeClass> someList1, List<SomeObject.RoomRateType> someList2)

  246         {

  247             string rphValue = String.Empty;

  248             List<SomeClass> someClassList = new List<SomeClass>();

  249             SomeClass someObjectVar = new SomeClass();

  250 

  251             foreach (SomeObject.SomeClass someObjectVar in someList1)

  252             {

  253                 if (someObjectVar.RPHSpecified)

  254                 {

  255                     rphValue = someObjectVar.RPH.ToString();

  256                     var linqQuery = from rrl in someList2

  257                                     where (rrl.RatePlanCode == rphValue) && (rrl.Rates[0].Base.AdditionalFeesExcludedIndicator == true)

  258                                     select rrl.RoomRateDescription.Name;

  259 

  260                     foreach (var linqItem in linqQuery)

  261                     {

  262                         someObjectVar.MoreDescription += linqItem;

  263                     }

  264 

  265                     someObjectVar.RphCode = someObjectVar.RPH;

  266                 }

  267             }

  268         }

the "rphValue" variable can be null or empty and although I know that what I am doing may not cause any trouble, I am sure in some more complex situation, the results will be dire

LINQ left join

Finally this site is back!

More LINQ (in Jeremy Clarkson's voice - POWER!!!!)

In the attempt of making a left join in LINQ with my recent work, I had to troll through the web and found the following from Naveen's blog


Essentially, in its most generic form of a LINQ Left Join is something like below


var LeftJoin = from linqObj1 in List1
join linqObj2 in List2
on linqObj1.Field1 equals linqObj2.Field1 into JoinedList1List2
from linqObj2 in JoinedList1List2t.DefaultIfEmpty()
select new
{
RequiredField1 = linqObj1.RequiredField22,
RequiredField2 = linqObj2!= null ? linqObj2.RequiredField45 : null
};

Fascination with LINQ

I was walking on my trail today and came across this item called, Lambda expression.

Speaking of which, "what are these?" one may ask.

It is basically simplification of LINQ query statements.

For example, the where-clause in LINQ.

From s In Someone _
Where s.Age > 0 _
Select s

can be simplified to a 1-liner.

Someone.Where(Function(s) s.Age > 0)

A point to note however is that my 1-liner is in VB.

Interesting, don't you think?

For more on this, visit Wiki-LINQ here

LINQ statement similar to SQL "In" statement

This is a very interesting LINQ feature I've stumbled across in my day to day work.

I can't quite post my work here but essentially, it is quite possible to imitate the "IN" statement in SQL.

What I meant by the "IN" query is for example;

SELECT * FROM [table]
WHERE id IN (1, 2, 3)

Using the extension method of "Contains" method, we achieve the SQL "In" method using LINQ. See below.

Have 2 list.
Eg: listOfElements1, listOfElements2

Depending on which list you would like to obtain the information from, perform a LINQ query on that list. For the purpose of this example, I am going to be using the LINQ query on the 1st list of elements.

VB Code
Dim linqToTest = From le1 In listOfElements1.ToArray() _
Where listOfElements2.Contains(le1.[elementName]) _
Select le1

A point to note is that 1 of my list is a list of primitive variables (listOfElements2) and the other (listOfElements1) is a list of objects.

Should you require joining of 2 list of similar types, primitive variables list with primitive variables list of the same type or list of objects that have 1 similar property, the LINQ "intersect" function should suffice.


References:
1. Rob Conery's blog; Wekeroad