问题描述
我有3个表:
Dealerships
------------
ID, Name, Website
Locations
------------
ID, DealershipID, Address, Ect.
Contacts
------------
ID, LocationID, Name, Ect.
所以关系表明,我们有谁拥有多个地点经销商(如:PA的杂草雪佛兰,杂草新泽西州的雪佛兰),然后每个位置都有自己的联系人(实施例:PA位置的管理者,NJ位置的经理)。我需要加入3个表在一起。这是我有:
So the relationship shows that we have dealerships who have multiple locations (Example: Weed Chevrolet of PA, Weed Chevrolet of NJ) and then each location has its own contacts (Example: Managers of PA location, Managers of NJ location). I need to join the 3 tables together. This is what I have:
var results = from d in entities.dealerships
join l in entities.locations on d.ID equals l.DealershipID
join c in entities.contacts on l.ID equals c.LocationID
select new
{
Name = d.Name,
Website = d.Website,
Address = l.Address + ", " + l.City + ", " + l.State + " " + l.Zip,
Contact = c.FirstName + " " + c.LastName,
WorkPhone = c.WorkPhone,
CellPhone = c.CellPhone,
HomePhone = c.HomePhone,
Email = c.Email,
AltEmail = c.AltEmail,
Sells = l.Sells
}
当我尝试绑定的结果到的BindingSource,然后我收到以下错误一个DataGridView:
When I attempt to bind results to a BindingSource and then to a DataGridView I receive the following error:
Unable to cast the type 'System.Nullable`1' to type 'System.Object'.
LINQ to Entities only supports casting Entity Data Model primitive types.
这里是什么地方?我是新加入的LINQ语句,使我相信我做错了什么。
What can it be? I am new to JOIN statements in LINQ so I am sure I am doing something wrong.
编辑:有数据在数据库中,这样的结果不能为空,只是为了澄清
There is data in the database so the results shouldn't be null, just to clarify
推荐答案
您已经接近,但我发现,你必须把它从LINQ到实体到LINQ到转换-Objects。首先,我不得不用投实体 AsEnumerable()
然后用了ToList()
。这使得它,所以我可以使用的功能,如的ToString()
和的String.Format()
。谢谢你带领我在正确的方向。下面是最终的代码:
You were close but I discovered that you have to convert it from LINQ-To-Entities to LINQ-To-Objects. First I had to cast the entities using AsEnumerable()
then use ToList()
. This made it so I could use functions like ToString()
and String.Format()
. Thanks for leading me in the right direction. Here is the final code:
var query = from d in entities.dealerships
from l in entities.locations.Where(loc => loc.DealershipID == d.ID).DefaultIfEmpty()
from c in entities.contacts.Where(cont => cont.LocationID == l.ID).DefaultIfEmpty()
where d.Keywords.Contains(keywords) || l.Keywords.Contains(keywords) || l.Sells.Contains(keywords) || c.Keywords.Contains(keywords)
select new
{
Dealership = d,
Location = l,
Contact = c
};
var results = (from r in query.AsEnumerable()
select new
{
Name = r.Dealership.Name,
Website = r.Dealership.Website,
Contact = r.Contact.FirstName + " " + r.Contact.LastName,
Address = r.Location.Address + ", " + r.Location.City + ", " + r.Location.State + " " + r.Location.Zip,
WorkPhone = r.Contact.WorkPhone,
CellPhone = r.Contact.CellPhone,
Fax = r.Contact.Fax,
Email = r.Contact.Email,
AltEmail = r.Contact.AltEmail,
Sells = r.Location.Sells
}).ToList();
bindingSource.DataSource = results;
这篇关于多个左加入LINQ到实体的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!