CODEWITHSUNDEEP
c#entity-frameworklinq-to-sqllinq-to-entities
Samuel
Samuel

Reputation: 27

Query Entity Framework using C# and Navigation Properties

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

Upvotes: 1

Views: 571

Answers (2)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

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.

Upvotes: 1

Antoine Pelletier
Antoine Pelletier

Reputation: 3316

An option would be to get the list of customers instead of just the customer's number :

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,
    listOfCustomers = cc.Customer.ToList() // <-Here, a list
}
)

Then you can use a loop :

foreach(var customer in myCustomers.listOfCustomers)
{
    var listOfLockers = customer.CustomerLockers.ToList();
}

But this is more a beginner's way, remember it's always better to take everything you need in a single query, like Stormcloack's answer.

This answer is just to show you how you can dig in the entitys the easy way.

Upvotes: 0

Related Questions