Linq Query using navigation properties and Where clause

Question

I am trying to compose a linq query using navigation properties. I am selecting properties from 3 entities:

• Lockers
• SectionColumn
• Contracts

I require ALL rows from the Lockers table where all the following conditions are met: the LockerTypeId = "308", .OutOfOrder != true, x.SectionColumn.SectionId == "52").

The query below without the condition x.SectionColumn.SectionId == "52" works and returns exactly what I require except rows with Section id of any value are returned as I would expect.

    from l in Lockers.Where(x => x.LockerTypeId == "308" && x.OutOfOrder != 
    true).DefaultIfEmpty()
               select new 
                {
                   ColumnNumber = l.ColumnNumber,
                   LockerTypeId = l.LockerTypeId,
                   OutOfOrder = l.OutOfOrder,
                   Rented = l.Contracts.Select(x => x.Contract_ID < 0 ?                   
                     false : true).FirstOrDefault(),
                   Section = l.SectionColumn.SectionId
                   }

When I add the condition 'x.SectionColumn.SectionId == "52"' as below I get the error "The cast to value type 'System.Int32' failed because the materialized value is null". Either the result type's generic parameter or the query must use a nullable type" in linqpad. SectionId is a string (varchar in SQL Server).

    from l in Lockers.Where(x => x.LockerTypeId == "308" && x.OutOfOrder != 
    true).DefaultIfEmpty()

I would be grateful for assistance in correctly writing this query.

Answer 1

First off, your code might be a little more straight forward if you stick to pure LINQ. In that case, your code should look something like the following.

 var results = from l in Lockers
               where l.LockerTypeId == "308" && l.OutOfOrder != true && l.SectionColumn.SectionId == "52"
               select new 
               {
                    ColumnNumber = l.ColumnNumber,
                    LockerTypeId = l.LockerTypeId,
                    OutOfOrder = l.OutOfOrder,
                    Rented = l.Contracts.Select(x => x.Contract_ID < 0 ? false : true).FirstOrDefault(),
                    Section = l.SectionColumn.SectionId
               }

If l.SectionColumn.SectionId represents valid navigational properties and is of type string, then this should work correctly.

You really haven't done a thorough job of describing the issue (and it looks like you didn't stick around to field questions), but if l.SectionColumn is nullable, you should be able to update your code to something like this.

 var results = from l in Lockers
               let sectionId = (l.SectionColumn != null) ? l.SectionColumn.SectionId : null
               where l.LockerTypeId == "308" && l.OutOfOrder != true && sectionId == "52"
               select new 
               {
                    ColumnNumber = l.ColumnNumber,
                    LockerTypeId = l.LockerTypeId,
                    OutOfOrder = l.OutOfOrder,
                    Rented = l.Contracts.Select(x => x.Contract_ID < 0 ? false : true).FirstOrDefault(),
                    Section = l.SectionColumn.SectionId
               }