none
Null checking relations (where product.Category == null...) RRS feed

  • Question

  • Hi all!

    I've found myself going mad because it's not possible to null check a relation in a LINQ query. For example, the below is not possible because the null check will always check against a Linq.QueryRef object that will always exist.

    SomeDataContext context = new SomeDataContext();
    var data = from product in context.Products
                   where product.Category == null
                   select product;

    What i would like this check to do is to grab all the products in our "database" and return those that are pointing to, or just lack the FK to a Category entry.

    The "== null) will always yield true, as stated above, because it'll do the null check against the QueryRef object and not the actual "does the category that this product points to, exist?" check.

    Instead I've had to do a work-around:

    var data = from product in context.Products
                   where !context.Categories.Any(c => c.Id == product.CategoryId)
                   select product;

    To be honest, the null check should generate the same LINQ as the above solution does, but it doesn't, as stated. As a programmer, doing a null check comes more naturally than doing a nested .Any check on the .Where clause, and isn't that what we should be aiming for? That code that comes more naturally should be the one that works.
    Tuesday, April 8, 2008 4:57 PM

Answers

  • I agree.

    Sql Server allows this design and can handle it.

    Linq To Sql have troubles if FK is equal PK.

    This is all I can do since I'm a customer too.

    regards

    Philipp

    Wednesday, April 9, 2008 6:27 PM

All replies

  • Can you please confirm that the Products table in your database indeed has rows with CategoryID value null?

     

    Thanks,

     

    --Samir

     

    Tuesday, April 8, 2008 9:53 PM
  • Hi,

     

    I tested three cases and all went fine

     

    Code Snippet

    var DC = new DBDataContext();

    // normal property nullable

    // private string _Text2;

    var qry1 = from o in DC.TestWithNulls

    where o.Text2 == null

    select o;

    DisplayData(qry1);

    Console.WriteLine();

    // 1:n relation

    // private EntitySet<TestWithNull2> _TestWithNull2s;

    // can't test for null since it's treated as IList<TestWithNull2>

    var qry2 = from o in DC.TestWithNulls

    where o.TestWithNull2s.Count == 0

    select o;

    DisplayData(qry2);

    Console.WriteLine();

    // n:1 relation

    // private EntityRef<TestWithNull> _TestWithNull;

    var qry3 = from o in DC.TestWithNull2s

    where o.TestWithNull == null

    select o;

    DisplayData2(qry3);

    Console.WriteLine();

    Console.ReadKey();

     

     

    Is product.CategoryId define as Nullable=True in O/R designer?

    If change this to false the third query will provide no results.

     

    regards

    Philipp

    Tuesday, April 8, 2008 10:24 PM
  • Ok, i made the post in a haste but now when recreating it i know that the database would stop me from having a PK to FK that didn't exist.. however, when looking closer on the problem and the database i've figured out what he's done. I don't know if it's correct or not but:

     

    There are 2 tables, let's say A and B

    A has 2 columns that define the PK, and B has the same columns as PK and there's a 1:1 relation set up between these two.

     

    Now, since they're a 1:1 relationship, the sql server can't stop the user from entering an A that's 1:1 to a B that doens't exist yet since a user can't insert data into 2 separate tables at the same time.

     

    So, LINQ's null check isn't working when there's a 1:1 relationship in a database between 2 tables. There's no way for me to check if A.B is null or not without doing  a select on B where B.PK == A.PK to grab A's B if it exists and do failsafe checking if it doesn't.

     

    Below is a ToyShop database LINQ example. The Products and ProductDescriptions both have Id and Name fields, the Id and Name field is set as PK and a relation is set up between them as a 1:1. What i want to do in the below example is get all the products who have ProductDescriptions that exist. However, the null check is done against the LINQ queryable object and not wether or not the product's description exists, which is what i want.

     

    Code Snippet

    ToyShopDataContext context = new ToyShopDataContext();

    var result = from product in context.Products

    where product.ProductDescription != null

    select product;

     

     

    In other words, LINQ does not support databases with multi-column PKs and relations.
    Wednesday, April 9, 2008 7:32 AM
  • What L2S can't handle is if you FKs are the PKs of the second table.

    If you have separate FKs in the second table and change the cardinality of the relation to OneToOne then L2S will produce the right results.

    In this case the query

    var qry1 = from t1 in dc.Test1s

    where t1.Test2s != null

    select t1;

    is translated to

    SELECT [t0].[ID11], [t0].[ID12], [t0].[Text1]
    FROM [dbo].[Test1] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[FK1], [t1].[FK2]
        FROM [dbo].[Test2] AS [t1]
        ) AS [t2] ON ([t2].[FK1] = [t0].[ID11]) AND ([t2].[FK2] = [t0].[ID12])
    WHERE [t2].[test] IS NOT NULL
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

    and gives the right result.

     

    But I see no way to get it run when the FK and PK are the same fields. Except including a subquery as you have done with where !context.Categories.Any(c => c.Id == product.CategoryId)

     

    regards

    Philipp

    Wednesday, April 9, 2008 4:01 PM
  • Nice to see it can handle the OneToOne when using separate fields. But still, the person designing the DB is using, in many of the cases, dual fields merged as 1 PK and that PK is reflected in another table.

    To use the previous example: if a product has a dual column PK of 01Rambo (Id, Name), the ProductDescription would have the same PK, 01Rambo, (Id, Name) and it's a OneToOne relationship.

    This is not how i would've designed the database personally but it's out of my hands, and, since it's a viable database design, LINQ to SQL should support it.
    Wednesday, April 9, 2008 5:16 PM
  • I agree.

    Sql Server allows this design and can handle it.

    Linq To Sql have troubles if FK is equal PK.

    This is all I can do since I'm a customer too.

    regards

    Philipp

    Wednesday, April 9, 2008 6:27 PM