Archive for the ‘LINQ’ Category

LINQ and Dynamic Query – Comparing DateTime value

October 20, 2011

In one of our development, we end up with the situation that needs to check the DateTime value in Dynamic LINQ query. Trying different ways out there and googling a bit, finally got some useful references that helped us to resolve the same

Hope, you would love reading!

 

Example:

TestEntities te = new TestEntities();

string dateString = “12/01/2010”;

 

Used the following overload for “.Where” :

# 1:

IQueryable<T>.Where(Expression<Func<State, bool>> predicate)

var dateTest = te.SiteLogs

.Where(String.Format(“it.TimeStamp >= DATETIME ‘{0}'”, Convert.ToDateTime(dateString).ToString(“yyyy-MM-dd HH:mm”)))

.Select(“it.IPAddress”);

 

# 2:

IQueryable.Where(string predicate, params object[] values)

var dateTest1 = te.SiteLogs

.Where(“it.TimeStamp >= @0”,  Convert.ToDateTime(dateString))

.Select(“it.IPAddress”);

 

Observations:

In # 1,

  • We need to say the “Time” format also, that is compulsory. Otherwise it would throw an error as well.  And again, the syntax with “DATETIME” is wired 🙂
  • The resultant date format should be “yyyy-MM-dd “

 

In # 2,

Its clean and simple. I like this syntax. Here you can add any number of Params like @0, @1 & so on

 

References:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

http://msdn.microsoft.com/en-us/library/bb399176.aspx

Lazy in C#, Lazy Loading, Eager Loading and Explicit Loading in Entity Framework

August 12, 2011

Lazy in C#;

In C# 4.0 a new type was introduced to support Lazy Loading. This helper type is great for types that require a long time to initialize.

Example:

new Car();

we can do

new Lazy();

This means that the CLR will load the type when a property is actually accessed.

About Lazy Loading, Eager Loading and Explicit Loading, here is a nice article:

http://weblogs.asp.net/dotnetstories/archive/2011/03/10/lazy-loading-eager-loading-explicit-loading-in-entity-framework-4.aspx

Entity Framework : Exception using OrderBy() in Dynamic LINQ

August 12, 2011

Using OrderBy(“ColumnName”)

Exception : ‘ColumnName’ could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier!

The solution : Always, should not miss “it” to prefix there in all the Dynamic LINQ. Hence, OrderBy(“it.ColumnName”)

NULL check results differently in LINQ To SQL (L2S) and LINQ To EF (L2E)

February 14, 2011

Here are some interesting gotchas i would like to share that are all confirmed with @jimwooley and @julielerman

As i learnt that L2S & L2E uses different parser to generate the TSQL, thought of checking them mainly with “null” in SELECT & WHERE clause respectively. Surprisingly, i got great differences and listed them here.

LINQ To SQL

I have used Table “Orders” from DB NorthWind

Example: var result = from ord in Orders select ord.ShipRegion;

In SELECT statement

LINQ SQL Equivalent
select ord.ShipRegion ?? “Lara” SELECT COALESCE([t0].[ShipRegion],@p0) AS [value]
select ord.ShipName == null ? “Lara” : ord.ShipName SELECT (CASEWHEN [t0].[ShipName] IS NULL THEN CONVERT(NVarChar(40),@p0) ELSE [t0].[ShipName] END) AS [value]
select Object.Equals(ord.ShipRegion, “Lara”) SELECT (CASEWHEN ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0) THEN 1 WHEN NOT (([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)) THEN 0 ELSE NULL END) AS [value]
select Object.Equals(ord.ShipRegion, null) SELECT (CASEWHEN [t0].[ShipRegion] IS NULL THEN 1 ELSE 0 END) AS [value]

In WHERE condition

LINQ SQL Equivalent
where Object.Equals(ord.ShipRegion, null) WHERE [t0].[ShipRegion] IS NULL
where Object.Equals(ord.ShipRegion, “Lara”) WHERE ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)
where String.Equals(or.ShipRegion, “Lara”) WHERE ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)
where ord.ShipRegion == (ord.ShipRegion ?? “Lara”) WHERE [t0].[ShipRegion] = (COALESCE([t0].[ShipRegion],@p0))
where ord.ShipRegion == (ord.ShipRegion == null ? “Lara” : ord.ShipRegion) WHERE [t0].[ShipRegion] = ((CASE WHEN [t0].[ShipRegion] IS NULL THEN CONVERT(NVarChar(15),@p0) ELSE [t0].[ShipRegion] END))

Note: DECLARE @p0 NVarChar(1000) = ‘Lara’ Gotchas:

  • ?? => COALESCE()
  • ? => CASE WHEN….END

LINQ To Entity Framework

Used a custom table: Vendor

In SELECT statement

· select (re.VendorName ?? null);
· select (re.VendorName == null ? 
"N/A" : re.VendorName);
SELECT [Extent1].[VendorName] AS [VendorName]
select (re.VendorName.Length > 0 ? 
re.VendorName : "N/A");
SELECT CASE WHEN ((LEN([Extent1].[VendorName])) > 0) THEN [Extent1].[VendorName] ELSE N’N/A’ END AS [C1]

In WHERE condition

DateTime? dt = null; 

where DateTime.Equals(res.UpdatedDateTime, dt)

  • If dt is “null”

WHERE [Extent1].[UpdatedDateTime] IS NULL

  • If dt is “not null”, the following exception for all primitive types:

Unable to cast the type ‘System.Nullable`1’ to type ‘System.Object’. LINQ to Entities only supports casting Entity Data Model primitive types.

String defaultVal = null;

…….

where Object.Equals(re.VendorName, defaultVal)
WHERE [Extent1].[VendorName] = @p__linq__0
· from re in obj.Vendors
  where Object.Equals(re.VendorName, null)
      select re.VendorId;

· from re in obj.Vendors
  where (re.VendorName.Equals(null))
       select re. VendorId;
If we keep “null” directly, then the following weird TSQL for both the LINQ: SELECT CAST(NULL AS int) AS [C1] FROM  ( SELECT 1 AS X ) AS [SingleRowTable1] WHERE 1 = 0
where re.VendorName.Equals(null) || re.VendorId > 0
Consider only VendorId and ignore the other 😦 WHERE [Extent1].[VendorId] > 0
where (!re.VendorName.Equals(null))
Ignores WHERE keyword in TSQL generation 😦
· where (re.UpdatedDateTime == 
(re.UpdatedDateTime ?? dt))

· where (re.UpdatedDateTime == 
(re.UpdatedDateTime == null ? 
dt : re.UpdatedDateTime))
WHERE [Extent1].[UpdatedDateTime] = (CASE WHEN ([Extent1].[UpdatedDateTime] IS NULL) THEN @p__linq__0 ELSE [Extent1].[UpdatedDateTime] END)

Gotchas:

  • For both ?? and ? => CASE WHEN….END in “where” clause only, but ignored in “select” clause
  • Weird TSQL equivalent with .Equals(null)

If anybody has any special thoughts, please comment 🙂