none
Possible Bug in Linq to SQL RRS feed

  • Question

  • Hello all,

     

    I have the following query that should work in normal instances (unless me understanding is off), but when I executed it, a part of the queries condition is omitted in the SQL statement sent to the server, thus causing an error. 

     

    var q = from o in db.OfficeSymbols

    where o.ParentID != null

    group o by o.ParentID into g

    select new {

    Name = g.Key,

    Count = g.Count()

    };

     

    which creates the following SQL statement:

     

    SELECT COUNT(*) AS [Count], [t0].[ParentID] AS [Name]
    FROM [dbo].[tbOfficeSymbols] AS [t0]
    GROUP BY [t0].[ParentID]

     

    if you look closely, the "where o.ParentID != null" is not present in the condition. 

     

    So, is this an intended behavior, am I missing something here, should I give up on ever seeing gas prices of $1.52 per gallon?  You decide.

     

    - Rashad Rivera

      www.omegusprime.com

    Sunday, March 30, 2008 6:14 PM

Answers

  • First, for LINQ To SQL, you should use this forum.

     

    Second, when I do this with Northwind:

    Code Snippet

    var q = from p in context.Products

            where p.CategoryID != null

            group p by p.CategoryID into g

            select new { g.Key, Count = g.Count() };

     

     

    I have this sql request:

    Code Snippet
    SELECT COUNT(*) AS [Count], [t0].[CategoryID] AS [Key]
    FROM [dbo].[Products] AS [t0]
    WHERE [t0].[CategoryID] IS NOT NULL
    GROUP BY [t0].[CategoryID]

     

     

    Now, if I take CategoryID not nullable in DB, the same LINQ query generates this sql request:

    Code Snippet

    SELECT COUNT(*) AS [Count], [t0].[CategoryID] AS [Key]
    FROM [dbo].[Products] AS [t0]
    GROUP BY [t0].[CategoryID]

     

     

    So in your case, I think your column ParentID doesn't allow null.

     

    Sunday, March 30, 2008 8:22 PM

All replies

  • First, for LINQ To SQL, you should use this forum.

     

    Second, when I do this with Northwind:

    Code Snippet

    var q = from p in context.Products

            where p.CategoryID != null

            group p by p.CategoryID into g

            select new { g.Key, Count = g.Count() };

     

     

    I have this sql request:

    Code Snippet
    SELECT COUNT(*) AS [Count], [t0].[CategoryID] AS [Key]
    FROM [dbo].[Products] AS [t0]
    WHERE [t0].[CategoryID] IS NOT NULL
    GROUP BY [t0].[CategoryID]

     

     

    Now, if I take CategoryID not nullable in DB, the same LINQ query generates this sql request:

    Code Snippet

    SELECT COUNT(*) AS [Count], [t0].[CategoryID] AS [Key]
    FROM [dbo].[Products] AS [t0]
    GROUP BY [t0].[CategoryID]

     

     

    So in your case, I think your column ParentID doesn't allow null.

     

    Sunday, March 30, 2008 8:22 PM
  • Thanks Matthieu,

     

    That answers the "why".  But how about the work around for columns where NULL is allowed?  Or did MS miss this condition? 

     

    I still feel that it does not make scence for the TSQL tranlator to ignore a condition.  It should at the minimum thrown an exception.

     

    - Rashad

    Saturday, April 12, 2008 8:48 PM
  • I think what Matthiew demonstrated is that the TSQL does carry over the condition on columns that allow null; it only omits it for those that don't allow it, because there will be no records with null values.

     

    Perhaps the critical point here, though, is that LINQ is relying on the objects in your DBML to determine what is nullable and what is not.  If you have set up your LINQ to SQL classes, and then you make a change in your SQL DB to make a column nullable, your LINQ to SQL type will still be set to not nullable, unless you (a) remove the object and re-add it from server explorer, or (b) change the properties for the field in question to match the changes you made to the DB.

     

    That's how I understand it, anyway.

    Tuesday, April 15, 2008 3:06 AM
  •  

    I see what I did wrong now.  It helps when you know more about how Linq to SQL works. 

     

    I build my class (that inherits from the DataContext), by hand and did not know that I needed to use the "System.Nullable<int>" return type for my column deffinitions (AKA class properties), instead of just "int".  When I looked at the automatic class generated by Visual Studio, I noticed the difference.  Leason learned.

     

    - Rashad Rivera

    Wednesday, April 16, 2008 6:37 PM
  •  

    an even better question....

    why does linq always use count(*) even if you tell him to actually do a count(fieldname).

    and i just hope that as far as performance on the sql server is concerned it doesn't really matter...but still...it doesn't really do what you tell him to do.

    Friday, May 30, 2008 7:40 AM
  • I think that is like saying "count the number of steering wheels that pass on this road," and the other person instead counts the number of cars that pass.  It is the same thing -- all you want is the number.

     

    I'm no SQL expert, but I imagine it would make more sense to index a rowcount query than a count of every field.

     

    Friday, May 30, 2008 11:16 AM