我正在使用Entity Framework模型数据来处理来自数据库的数据(CRUD操作).我想从表中获取所有数据(不只是一个).
这是数据库模型：
我想从所有表中获取多个数据.
目前我正在使用显示的查询,但此查询的问题是我从Contact表中获取了多个值,而其他表只显示了一个结果.有人知道为什么我的查询不起作用以及如何从表中获取所有多个数据.
ContactsEntities db = new ContactsEntities();
//get all contacts
public JsonResult GetAll()
{
var data = (from c in db.Contacts
from e in db.Emails.Where(x => x.id == c.id).DefaultIfEmpty()
from p in db.Phones.Where(x => x.id == c.id).DefaultIfEmpty()
from t in db.Tags.Where(x => x.id == c.id).DefaultIfEmpty()
select new
{
id = c.id,phones = p.number,emails = e.email1,tags = t.tag1,firstname = c.firstname,lastname = c.lastname,address = c.address,city = c.city,bookmarked = c.bookmarked,notes = c.notes
}).ToList();
return Json(data,JsonRequestBehavior.AllowGet);
}
解决方法
我已经在你的模型上对它进行了测试：
var test1 = (from c in db.Contacts
join e in db.Emails
on c.id equals e.id_contact
join t in db.Tags
on c.id equals t.id_contact
join p in db.Phones on c.id equals p.id_contact
select new
{
id = c.id,notes = c.notes
}).ToList();
我试图一步解决这个问题,否则在test1之后添加它可以正常工作：
var result = (from contact in test1
group contact by contact.id into grp
select new
{
id = grp.Key,firstname = grp.First().firstname,lastname = grp.First().lastname,address = grp.First().address,city = grp.First().city,bookmarked = grp.First().bookmarked,notes = grp.First().notes,phones = grp.Where(x => x.phones != null).Select(x => x.phones).Distinct().ToArray(),emails = grp.Where(x => x.emails != null).Select(x => x.emails).Distinct().ToArray(),tags = grp.Where(x => x.tags != null).Select(x => x.tags).Distinct().ToArray()
}).ToList();
如果你建立了它们之间的关系,它将被解析,这段代码将返回你想要的所有联系人：
1-创建新图表
2-添加这些表格,然后在每个电子邮件,标签和电话的“id_contact”上拖动联系人的ID
3-在sql Server上保存图表
4-在Visual Studio中重新创建模型
var contacts = (from c in db.Contacts
select c).ToList();
