Using Dynamic Linq to build custom entity framework queries

 

One great library that never made it to the framework is System.Dynamic.Linq. Basically it allows you to write dymamic linq expressions in context where you are not strongly typed.

Entity framework actually supports expressions provided outside System.Linq as long as they are not compiled and that it can actually be resolved to known members and SQL functions.

The library is open source and can be found on codeplex at https://dynamiclinq.codeplex.com/ or as a nuget package https://www.nuget.org/packages/System.Linq.Dynamic/

Install-Package System.Linq.Dynamic

Simple northwnd example:

 

var resTyped = ctx.Set<Category>().Where(V => V.CategoryName == "Beverages");

var resDynamic = ctx.Set<Category>().Where("CategoryName.Equals(@0)","Beverages");

How to check nullable types in dynamic linq?

If you use operators on nullable types to evaluate your conditions, you’ll end up with an error like this.

Operator ‘=’ incompatible with operand types ‘DateTime?’ and ‘Boolean’

Work around is to use the Equals method to compare your values.

var dateCheck = ctx.Set<Order>().Where("OrderDate.Equals(@0)", DateTime.Now);

var wontWork = ctx.Set<Order>().Where("OrderDate == @0", DateTime.Now);

 

If you need to check for null

var dateCheck = ctx.Set<Order>().Where("OrderDate.Equals(@0)", default(DateTime?));

Will generate the following SQL

{SELECT

    [Extent1].[OrderID] AS [OrderID],

    [Extent1].[CustomerID] AS [CustomerID],

    [Extent1].[EmployeeID] AS [EmployeeID],

    [Extent1].[OrderDate] AS [OrderDate],

    [Extent1].[RequiredDate] AS [RequiredDate],

    [Extent1].[ShippedDate] AS [ShippedDate],

    [Extent1].[ShipVia] AS [ShipVia],

    [Extent1].[Freight] AS [Freight],

    [Extent1].[ShipName] AS [ShipName],

    [Extent1].[ShipAddress] AS [ShipAddress],

    [Extent1].[ShipCity] AS [ShipCity],

    [Extent1].[ShipRegion] AS [ShipRegion],

    [Extent1].[ShipPostalCode] AS [ShipPostalCode],

    [Extent1].[ShipCountry] AS [ShipCountry]

    FROM [dbo].[Orders] AS [Extent1]

    WHERE [Extent1].[OrderDate] IS NULL}

 

If you really want to use operators, or if you need to evaluate something else than equality. Just use the following trick : OrderDate.HasValue && OrderDate.Value < (@0)

Would generate the following

{SELECT

[Extent1].[OrderID] AS [OrderID],

[Extent1].[CustomerID] AS [CustomerID],

[Extent1].[EmployeeID] AS [EmployeeID],

[Extent1].[OrderDate] AS [OrderDate],

[Extent1].[RequiredDate] AS [RequiredDate],

[Extent1].[ShippedDate] AS [ShippedDate],

[Extent1].[ShipVia] AS [ShipVia],

[Extent1].[Freight] AS [Freight],

[Extent1].[ShipName] AS [ShipName],

[Extent1].[ShipAddress] AS [ShipAddress],

[Extent1].[ShipCity] AS [ShipCity],

[Extent1].[ShipRegion] AS [ShipRegion],

[Extent1].[ShipPostalCode] AS [ShipPostalCode],

[Extent1].[ShipCountry] AS [ShipCountry]

FROM [dbo].[Orders] AS [Extent1]

WHERE ([Extent1].[OrderDate] IS NOT NULL) AND ([Extent1].[OrderDate] &lt; convert(datetime2, '2016-09-21 13:28:29.7057100', 121))}

 

Submit a Comment

Your email address will not be published. Required fields are marked *

Share This