none
Query difficulty with nullable field RRS feed

  • Question

  • I have a main table "Items" that has FKs to several other tables that do not allow null values. If I do this:

     

    var items = from i in me.items
                                join c in me.Categories on i.categoryID equals c.categoryID
                                join b in me.Brands on i.brandID equals b.brandID
                                join o in me.Origins on i.originID equals o.originID
                                join s in me.Status on i.statusID equals s.statusID
                                select new
                                {
                                    i.name,
                                    b.brandName,
                                    i.model,
                                    i.serialNo,
                                    i.aiNumber,
                                    o.originCountry,
                                    i.cost,
                                    c.categoryName
                                };
    
                    dgvMaterial.DataSource = items.ToList();

     


    The data appears correctly so I think I have this right by using joins. But if I include:

    join t in me.types on i.typesID equals t.typesID

    in the query and t.typeName in the new anonymous type the datagridview only shows items that have data in the nullable field typeName i.e it ignores all items that have nothing in the typeName field. What do I need to include to make this work? I haven't succeeded unfortunately.


    :-( Still trying to program
    Saturday, October 15, 2011 12:24 PM

Answers

  • Hi Jonsey,

    Try to create a subresult and do the DefaultIfEmpty method on it, so it will return something even if there isn't a match.  Like this:

    var items = from i in me.items
                                join c in me.Categories on i.categoryID equals c.categoryID
                                join b in me.Brands on i.brandID equals b.brandID
                                join o in me.Origins on i.originID equals o.originID
                                join s in me.Status on i.statusID equals s.statusID
                                join t in me.types on i.typesID equals t.typesID into sr 
    from x in sr.DefaultIfEmpty()
                                select new
                                {
                                    i.name,
                                    b.brandName,
                                    i.model,
                                    i.serialNo,
                                    i.aiNumber,
                                    o.originCountry,
                                    i.cost,
                                    c.categoryName,
                                    t.TypeName
                                };



    Tom Overton
    • Marked as answer by Jonsey Saturday, October 15, 2011 2:24 PM
    Saturday, October 15, 2011 1:15 PM

All replies

  • Hi Jonsey,

    Try to create a subresult and do the DefaultIfEmpty method on it, so it will return something even if there isn't a match.  Like this:

    var items = from i in me.items
                                join c in me.Categories on i.categoryID equals c.categoryID
                                join b in me.Brands on i.brandID equals b.brandID
                                join o in me.Origins on i.originID equals o.originID
                                join s in me.Status on i.statusID equals s.statusID
                                join t in me.types on i.typesID equals t.typesID into sr 
    from x in sr.DefaultIfEmpty()
                                select new
                                {
                                    i.name,
                                    b.brandName,
                                    i.model,
                                    i.serialNo,
                                    i.aiNumber,
                                    o.originCountry,
                                    i.cost,
                                    c.categoryName,
                                    t.TypeName
                                };



    Tom Overton
    • Marked as answer by Jonsey Saturday, October 15, 2011 2:24 PM
    Saturday, October 15, 2011 1:15 PM
  • That's fantastic Tom, many thanks, works a treat. I would never had thought of that or seen it in the books I have. 

    Would you mind letting me know if I my query is OK with all the joins and whether the problem you solved for me, is this quite current a subresult to resolve the problem or is my database design suspect?

    Many thanks for you help


    :-( Still trying to program
    Saturday, October 15, 2011 2:28 PM