Query Entity Framework using C# and Navigation Properties

Question

First of all I am new to both C# and EF.

I have created a number of entities with the the Model designer in VS 2015 CE and set the relationships.

I would like to query the entities to return all the customers for a specific Contract (e.g. Contract_ID = 1), along with related properties from the CustomerLocker and ContractCustomer entities (For the CustomerLocker Entity if they are present, or null if they are not). I also have the LockerNumber value from the Contract entity (e.g. 100).

I would be grateful if someone can help with the LINQ query required to select the properties I require. I would prefer to be able to use navigation properties if possible.

So far I am able to select the customers but not able to select properties from the CustomerLocker entity.

    var myCustomers = (from cc in context.ContractCustomers
    where cc.Contract_ID.Equals(contractID)
    select new
    {
    Licencee = cc.IsLicencee,
    Added = cc.AddedDate,
    Firstname = cc.Customer.FirstName,
    Lastname =  cc.Customer.LastName,
    DOB = cc.Customer.DateOfBirth,
    Postcode = cc.Customer.PostCode,
    CustomerNumber = cc.CustomerNumber             
    }
    )

entities shown in VS Model Designer

Answer 1

You could get the HasCard from CustomerLockers by filtering on LockerNumber;

CustomerLockers = cc.Customer.CustomerLockers

The query;

var myCustomers = (from cc in context.ContractCustomers
    where cc.Contract_ID.Equals(contractID)
    select new
    {
        Licencee = cc.IsLicencee,
        Added = cc.AddedDate,
        Firstname = cc.Customer.FirstName,
        Lastname = cc.Customer.LastName,
        DOB = cc.Customer.DateOfBirth,
        Postcode = cc.Customer.PostCode,
        CustomerNumber = cc.CustomerNumber,
        CustomerLockerHasCard = cc.Customer.CustomerLockers
                        .Where(x => x.LockerNumber == 1000)
                        .Select(x => x.HasCard)
    }
)

Also, I suggest you to define model classes as known type instead of using anonymous type.