Different SQL between VB.NET and C# LINQ to SQL SOLVED!!

Hi All,

Following my previous post I have solved this issue. Somehow accidently I discovered that if the field has allow nulls set on it VB.NET will add the Where Coalesce in your query hence in my case slowing it down dramatically. It seems that C# does not do this. So finally I have my VB.NET sql comming out exactly the same as in C# simply by turning off allow nulls on my database column... Still seems like odd behaivour to me but for now it all works fine.


Thanks
Stefan

5 Comments

  • The behavior is caused by the following: VB.NET and C# have different rules with respect to comparing to null/Nothing: in VB.NET it's always false if I'm not mistaken, in C# it's not. In in-memory queries over an IEnumerable, this has effect on the results: you might get different results in VB than in C# with the same Linq to Objects query.

    MS thought it was best if queries on the database would mimic the same behavior as if the query was an in-memory query. This means that NULL comparisons have to act the same as if they were executed in-memory. This gives the differences in the queries.

    In practise, this of course isn't what you want: querying a database is something you KNOW you're doing at that point, especially since there are numerous aspects of Linq which work differently in the database.

    So this is the cause of the difference. I'll answer your question via email today (hopefully) :).

  • Thanks alot Frans,

    Good to get this cleared up now :) I appreciate all your help.


    Thanks
    Stefan

  • Hi Stephan,

    Can you post your assemblies that you used to compare the queries? It will be really helpful for us to fix this issue.

    Regards,
    Avner Aharoni
    Microsoft

  • Hello All,

    It looks like I figured out the root cause of this. In my comparison I am not using the .Value on the nullable type.

    If I were to write a query like so:

    From p In db.people Where p.age = 10 and age was a nullable field, vb.net would add the where coalesce part to the query, but if I wrote it as From p In db.people Where p.age.Value = 10.


    So it looks like in the end it was purely me that was causing this issue. :(


    Sorry to all
    Stefan

  • I think this problem is due to the fact that the logical operators in VB is considered to be three-state, any "equal to", "greater then", or "less then" operation in VB can result in either True, False, or NULL. This means that the comparison will result in a nullable boolean which is causing the Linq to use the coalesce operator to convert it back to a non-nullable boolean. I would consider this to be a bug.

Comments have been disabled for this content.