实体SQL和多对多关系 [英] Entity SQL and many to many relationships

问题描述

我有一个EntityDataSource，我需要提供一个where子句，所以根据我的理解，它必须是一个有效的Entity SQL语句。这是情况。

假设您有一个Contact和ContactType表。由于联系人可以有多种类型，并且类型可以有多个联系人，因此我们在数据库中使用多对多关系。这个表是ContactToType，它有一个由contact_id和contacttype_id外键组成的复合主键。

如上所述，联系人可以有多种类型，但每种类型也有一个生物。例如，因此一个联系人在数据库中列为远足者和滑雪者。他的远足轮廓将具有生物和滑雪配置文件。在数据库方面，这是通过向ContactToType表添加生物字段来实现的。

现在假设我想查询所有徒步旅行者的联系人。在SQL中，这是一个简单的查询，可以写成如下：

选择

c 。 contact_name ， ct 。 contacttype_name ， ctt 。 bio

来自

联系人c ， contacttype ct ， contacttotype ctt

其中

c 。 contact_id = ctt 。 contact_id 和 ct 。 contacttype_id = ctt 。 contacttype_id 和 ct 。 contacttype_id = the_hiker_id

我意识到这应该写使用JOIN关键字但你明白了。

现在回答这个问题。实体框架表示ContactToType表，其中包含联系人和contacttype的所有三个字段（contact_id，contacttype_id，bio）和两个导航属性。如何编写实体SQL查询以返回上述查询的结果？

解决方案

如果不是，则不会看到ContactToType实体ContactToType表只有两个字段（contact_id和contacttype_id）。原因是EDM能够在隐藏时加入映射来表示多对多关系。

使用（实体上下文=新实体（））
{
var result =（from c in context.Contact
join t in context.ContactType On c.contact_id Equals t.contacttype_id
其中（t.ct.contacttype_id = the_hiker_id）
选择c）。不同（）;
}

I have an EntityDataSource that I need to give a where clause, so from what I understand, it must be a valid Entity SQL statement. Here is the situation.

Suppose you have a Contact and ContactType table. Since a contact can have many types and a type can have many contacts we use a many to many relationship in the database. The table for this is ContactToType which has a composite primary key consisting of the contact_id and contacttype_id foreign keys.

As noted, contacts can have multiple types, but they also have one bio per type. So for example, so one contact is listed in the database as a hiker and a skier. His hiking profile would have a bio as well as his skiing profile. On the database side, this is achieved by adding a bio field to the ContactToType table.

Now suppose I want to query all of the contacts who were hikers. In SQL, this is an easy query and could be written something like this:

select

 

c.contact_name, ct.contacttype_name, ctt.bio

from

 

contact c, contacttype ct, contacttotype ctt

where

 

c.contact_id = ctt.contact_id and ct.contacttype_id = ctt.contacttype_id and ct.contacttype_id = the_hiker_id

I realize this should be written with the JOIN keyword but you get the idea.

Now to the question. The Entity Framework represents the ContactToType table with all three fields (contact_id, contacttype_id, bio) and two navigation properties for contact and contacttype. How do I write an Entity SQL query to return the results that the above query would?

解决方案

You will not see ContactToType entity if ContactToType table has only two fields (contact_id and contacttype_id). The reason is that EDM has the ability to represent many-to-many relationships while hiding to join the mappings.

using (Entities context = new Entities())
{
var result = (From c In context.Contact
             Join t In context.ContactType On c.contact_id Equals t.contacttype_id
             Where (t.ct.contacttype_id = the_hiker_id) 
             Select c).Distinct();
}

这篇关于实体SQL和多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！