开发者

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.

开发者_C百科

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?


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


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;


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜