0

In my MVC project, I am using EF where entities (edmx) are generated from Universe DB by making use of Rocket U2. After the edmx generation, I just felt like it will be the straight forward business of data retrieval with LINQ but it isn't as I stumbled upon a problem straight away with my very simple and first LINQ statement which is as follow 

1. var test1 = userRepo.QueryAll().Where(x => x.Code == model.UserName);
// generated SQL
FROM User AS Extent1
WHERE (Extent1.Code = ?) OR ((Extent1.Code IS NULL) AND (? IS NULL))}

Simple isn't it? Actually it isn't as what I have experienced so far is the SQL statement generated by LINQ is quite weird when I am passing a property value to the where clause while the following statement execute completely fine where I have hard coded value

2. var test1 = userRepo.QueryAll().Where(x => x.Code == "JK");
// generated SQL
FROM User AS Extent1
WHERE 'JK' = Extent1.Code}

Now, if i return back to my first LINQ query, I noticed, it will work if I enumerate the result before where clause i-e. as follow 

3. var test = userRepo.QueryAll().ToList().Where(x => x.Code == model.UserName);

Can please anyone explain what I am doing wrong here. Although, I can enumerate the result before applying filters but this is not something that would be appropriate for later development where I have to work with joins etc.

Above behavior is same even if work directly with the DbContext rather the individual repository.

Learning Curve
  • 1,449
  • 7
  • 30
  • 60
  • 1
    Try adding `Configuration.UseDatabaseNullSemantics = true;` to your `DbContext` constructor. – Ivan Stoev Oct 27 '16 at 12:50
  • Great Ivan. Thumbs up for you. Although, I have seen that answer somewhere while I was googling around but didn't try as in my previous projects i used same technique where I was working with SQL DB and never had an issue. Can you explain why such weird behavior? – Learning Curve Oct 27 '16 at 12:56
  • Normally there is no problem of not setting it, but looks like your db query provider has a bug (generates incorrect SQL and/or positional parameters - the db providers I'm working use **named** parameters). Setting the flag eliminates `IS NULL` expressions, hence the translator will use single parameter. – Ivan Stoev Oct 27 '16 at 13:20

0 Answers0