CODEWITHSUNDEEP
linqentity-framework
KentZhou
KentZhou

Reputation: 25553

How to write linq query based on EF?

Suppose I have three tables:

Person(pid, ...)
PersonAddress(pid, aid,...)
Address(aid, ...)

Then I want to get the person address like sql:

select a.* from address a join PersonAddress pa on a.addressID=pa.addressID 
where pa.personID = myPersonID

Use Entity Framework to create Entity model, then want to write a linq equivalent as above sql.

I tried it in following way:

 var addresses = this.GetAddress();
        var personaddresses = this.GetPersonAddress();

        var query = from ad in addresses
                    from pa in personaddresses
                    where ((ad.AddressID == pa.AddressID)&&(pa.PersonID==person.personID))
                    select ad;

but I got error. Or I try to start from:

var result = this.Context.Address;
var result = result.Join ....  //how to write linq in this way?

How to write the linq?

Upvotes: 0

Views: 2334

Answers (3)

mkedobbs
mkedobbs

Reputation: 4375

Assuming you have three entities: Person, PersonAddress and Address, here is a query that should meet your needs (this example assumes an Entity Framework context named context):

var values = context.PersonAddress.Where(pa => pa.Person.PersonId == myPersonId).Select(pa => pa.Address);

However, if the PersonAddress table exists as a pure many-to-many relationship table (i.e. contains only keys), you'd be better off setting up your Entity Framework model in such a way that the intermediate table isn't necessary, which would leave you with the much simpler:

var values = context.Person.Where(p => p.PersonId == myPersonId).Addresses;

Based on the additional feedback

Because you need to include the country table, you should originate your query from the Address table. In that case:

var values = context.Address.Where(a => a.PersonAddress.Where(pa => pa.Product.Id == myProductId).Count() > 0)

To include the Country table in the result:

var values = context.Address.Include("Country").Where(a => a.PersonAddress.Where(pa => pa.Product.Id == myProductId).Count() > 0)

Upvotes: 1

Craig Stuntz
Craig Stuntz

Reputation: 126547

You almost never use join in LINQ to Entities.

Try:

var q = from p in Context.People
        where p.PersonId == personId
        from a in p.Addresses // presumes p.Addresses is 1..*
        select a;

Upvotes: 1

bobwah
bobwah

Reputation: 2568

This is untested but if you have all of your relationships setup and you create the model (I have used Model as the name for this) from this you should be able to use the following:

var values = this.Model.Address.Select(a => a.PersonAddress.Where(pa => pa.Id == myPersonID));

Upvotes: 2

Related Questions