多个左加入LINQ到实体 [英] Multiple Left Join LINQ-to-entities

问题描述

我有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到实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！