how to write linq query based on EF?

Question

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 try it in following way:

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

        var qurey = 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?

Answer 1

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));

Answer 2

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;

Answer 3

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)