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

You have to follow a course or a tutorial, if you haven't start coding yet. If you have written any code, show us how far you got. Remember : we are not here to code for you, but to solve SPECIFIC problems. You went through to help page ? Well... for now your question is too broad, but you can make it better — Antoine Pelletier
– Antoine Pelletier, Commented Jan 19, 2018 at 15:04

Emre Kabaoglu · Accepted Answer · 2018-01-19 17:01:10Z

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.

edited Jan 19, 2018 at 17:01

answered Jan 19, 2018 at 15:28

Emre Kabaoglu

13.2k4 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Samuel Over a year ago

Thanks for your assistance. How can I select specific properties from the CustomerLocker entity (e.g. HasCard) where the LockerNumber is 1000?

Antoine Pelletier Over a year ago

@Samuel ... That's another question, we can't accompany you and build your project together. That comment may be candidat for another question. Rembeber, one question at a time.

Samuel Over a year ago

Dear Antione Pelletier - this is notanother question as it is exactly what I asked in my original question - "related properties from the CustomerLocker and ContractCustomer entities".

Samuel Over a year ago

Dear Stormcloak - your answer is exactly what I required. Thank you kindly for your excellent assistance.

Antoine Pelletier · Accepted Answer · 2018-01-19 15:47:21Z

0

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.

answered Jan 19, 2018 at 15:47

Antoine Pelletier

3,3354 gold badges43 silver badges70 bronze badges

1 Comment

Samuel Over a year ago

Many thanks for your assistance. I am familiar with the option you have shown but I would like to use a more efficient query.

Collectives™ on Stack Overflow

Query Entity Framework using C# and Navigation Properties

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related