这让我疯了！
我尝试了很多方法（以及每种方式的多次迭代）但得到了相同的错误。
我在LINQPad中尝试了查询并获得了所需的结果。
场景：码头。 我想要一张带有船只细节的所有单据的清单，如果一艘船被注册为停留在滑道上。 如果在滑动处没有注册船只，则boatID字段可能为NULL（我知道这应该设置为密钥，但我尝试使用Linq来获得答案而不更改数据库）。 有一个带有滑动列表的“滑动”表，包括一个BoatId字段（当船只在滑道上注册时）。 第二张桌子是一张'Boat'牌桌，以BoatId作为钥匙和其他船只细节。
这是一个SQL查询（产生我想要的结果）：
Select s.SlipID, s.SlipNumber, s.Length, s.Electricity, s.Telephone, s.TV, b.BoatName+' ['+b.BoatType+', '+convert(varchar,b.BoatOverAllLength)+']' as boatDets, s.Status from Slip as s left outer join boat as b on s.BoatID = b.BoatId;这是给出错误的解决方案之一（但在LINQPad中有效）：
var slipDets6 = from s6 in db.Slips join b6 in db.Boats on s6.BoatId equals b6.BoatId into temp from jn in temp.DefaultIfEmpty() orderby s6.SlipNumber select new { SlipID = (int?) s6.SlipId, SlipNumber = s6.SlipNumber, Length = s6.Length, Electricity = s6.Electricity, Telephone = s6.Telephone, TV = s6.TV, BoatDets = jn.BoatName + " [" + jn.BoatType + ", " + jn.BoatOverAllLength + "]", Status = s6.Status };我收到的实际错误代码是：
无法将类型System.Nullable'1转换为System.Object类型。 LINQ to Entities仅支持转换EDM原语或枚举类型。
我已经深入研究了我在这个网站（和其他网站）上可以找到的解决方案，但我似乎正在做正确的事情。
This is driving me nuts!
I have tried a number of ways (and many iterations of each way) but get the same error.
I have tried the query in LINQPad and get the desired result.
Scenario: A marina. I want a list of all the slips with boat details, if a boat is registered as staying at the slip. If there is no boat registered at a slip then the boatID field could be NULL (I know this should be set up as a key but I am trying to use Linq to get to the answer without changing the data base). There is a 'Slip' table with a list of slips, including a BoatId field (for when a boat is registered at the slip). The second table is a 'Boat' table, with BoatId as the key and other boat details.
Here is a SQL Query (that produces the result I want):
Select s.SlipID, s.SlipNumber, s.Length, s.Electricity, s.Telephone, s.TV, b.BoatName+' ['+b.BoatType+', '+convert(varchar,b.BoatOverAllLength)+']' as boatDets, s.Status from Slip as s left outer join boat as b on s.BoatID = b.BoatId;Here is one of the solutions that gives an error (but works in LINQPad):
var slipDets6 = from s6 in db.Slips join b6 in db.Boats on s6.BoatId equals b6.BoatId into temp from jn in temp.DefaultIfEmpty() orderby s6.SlipNumber select new { SlipID = (int?) s6.SlipId, SlipNumber = s6.SlipNumber, Length = s6.Length, Electricity = s6.Electricity, Telephone = s6.Telephone, TV = s6.TV, BoatDets = jn.BoatName + " [" + jn.BoatType + ", " + jn.BoatOverAllLength + "]", Status = s6.Status };The actual error code I receive is:
Unable to cast the type System.Nullable'1 to type System.Object. LINQ to Entities only supports casting EDM primitive or enumeration types.
I have delved into as many solutions I could find on this site (and others) but I seem to be doing the right thing.
最满意答案
根据你的评论，问题是你试图从BoatName, BoatType and BoatOverAllLength create a string ，你不能格式化linq to entities的字符串linq to entities ，正如我之前所说（prev post，no3），你可以获取你需要的数据和然后在内存中select创建BoatDets字符串，所以这肯定有效：
var slipDets6 = (from s6 in db.Slips join b6 in db.Boats on s6.BoatId equals b6.BoatId into temp from jn in temp.DefaultIfEmpty() orderby s6.SlipNumber select new {s6, jn}) .ToList() .Select(u => new { SlipID = (int?)u.s6.SlipId, SlipNumber = u.s6.SlipNumber, Length = u.s6.Length, Electricity = u.s6.Electricity, Telephone = u.s6.Telephone, TV = u.s6.TV, BoatDets = u.jn == null ? "" : u.jn.BoatName + " [" + u.jn.BoatType + ", " + u.jn.BoatOverAllLength + "]", Status = u.s6.Status }) .ToList();或者，您可以将BoatName, BoatType and BoatOverAllLength作为属性获取，并且当您的查询被提取时，从该属性创建您需要的字符串，如下所示：
public class FlatSlip { public int? SlipID { get; set; } public string SlipNumber { get; set; } public string Length { get; set; } public string Electricity { get; set; } public string Telephone { get; set; } public string TV { get; set; } public string BoatName { get; set; } public string BoatType { get; set; } public string BoatOverAllLength { get; set; } public string Status { get; set; } public string BoatDets { get { return this.BoatName + " [" + this.BoatType + ", " + this.BoatOverAllLength + "]"; } } } var slipDets6 = from s6 in db.Slips join b6 in db.Boats on s6.BoatId equals b6.BoatId into temp from jn in temp.DefaultIfEmpty() orderby s6.SlipNumber select new FlatSlip() { SlipID = (int?)s6.SlipId, SlipNumber = s6.SlipNumber, Length = s6.Length, Electricity = s6.Electricity, Telephone = s6.Telephone, TV = s6.TV, BoatName = jn == null ? "" : jn.BoatName, BoatType = jn == null ? "" : jn.BoatType, BoatOverAllLength = jn == null ? "" : jn.BoatOverAllLength, Status = s6.Status };或者如果你坚持使用literal ：
public class Boat { public Boat() { } public Boat(string BoatName, string BoatType, string BoatOverAllLength) { this.BoatName = BoatName; this.BoatType = BoatType; this.BoatOverAllLength = BoatOverAllLength; } public string BoatName { get; set; } public string BoatType { get; set; } public string BoatOverAllLength { get; set; } public string BoatDets { get { return this.BoatName + " [" + this.BoatType + ", " + this.BoatOverAllLength + "]"; } } } var slipDets6 = (from s6 in db.Slips join b6 in db.Boats on s6.BoatId equals b6.BoatId into temp from jn in temp.DefaultIfEmpty() orderby s6.SlipNumber select new { s6, jn }) .ToList() .Select(u => new { SlipID = (int?)u.s6.SlipId, SlipNumber = u.s6.SlipNumber, Length = u.s6.Length, Electricity = u.s6.Electricity, Telephone = u.s6.Telephone, TV = u.s6.TV, BoatDets = jn == null ? new Boat() : new Boat(u.jn.BoatName, u.jn.BoatType, u.jn.BoatOverAllLength), Status = u.s6.Status }) .ToList();注意：在我的两个最后查询中， BoatDets属性不能在linq to entity ，并且当你的数据被提取到内存时它是可读的
according to your comment, the problem is where u try to create a string from BoatName, BoatType and BoatOverAllLength, you cant format a string in linq to entities, as i said before (prev post, no3), you can fetch data you need and then select in memory to create BoatDets string, so this, surely works:
var slipDets6 = (from s6 in db.Slips join b6 in db.Boats on s6.BoatId equals b6.BoatId into temp from jn in temp.DefaultIfEmpty() orderby s6.SlipNumber select new {s6, jn}) .ToList() .Select(u => new { SlipID = (int?)u.s6.SlipId, SlipNumber = u.s6.SlipNumber, Length = u.s6.Length, Electricity = u.s6.Electricity, Telephone = u.s6.Telephone, TV = u.s6.TV, BoatDets = u.jn == null ? "" : u.jn.BoatName + " [" + u.jn.BoatType + ", " + u.jn.BoatOverAllLength + "]", Status = u.s6.Status }) .ToList();or, you can fetch BoatName, BoatType and BoatOverAllLength as property, and when your query fetched, create string you need from that properties, something like this:
public class FlatSlip { public int? SlipID { get; set; } public string SlipNumber { get; set; } public string Length { get; set; } public string Electricity { get; set; } public string Telephone { get; set; } public string TV { get; set; } public string BoatName { get; set; } public string BoatType { get; set; } public string BoatOverAllLength { get; set; } public string Status { get; set; } public string BoatDets { get { return this.BoatName + " [" + this.BoatType + ", " + this.BoatOverAllLength + "]"; } } } var slipDets6 = from s6 in db.Slips join b6 in db.Boats on s6.BoatId equals b6.BoatId into temp from jn in temp.DefaultIfEmpty() orderby s6.SlipNumber select new FlatSlip() { SlipID = (int?)s6.SlipId, SlipNumber = s6.SlipNumber, Length = s6.Length, Electricity = s6.Electricity, Telephone = s6.Telephone, TV = s6.TV, BoatName = jn == null ? "" : jn.BoatName, BoatType = jn == null ? "" : jn.BoatType, BoatOverAllLength = jn == null ? "" : jn.BoatOverAllLength, Status = s6.Status };or if you are insisting on using literal:
public class Boat { public Boat() { } public Boat(string BoatName, string BoatType, string BoatOverAllLength) { this.BoatName = BoatName; this.BoatType = BoatType; this.BoatOverAllLength = BoatOverAllLength; } public string BoatName { get; set; } public string BoatType { get; set; } public string BoatOverAllLength { get; set; } public string BoatDets { get { return this.BoatName + " [" + this.BoatType + ", " + this.BoatOverAllLength + "]"; } } } var slipDets6 = (from s6 in db.Slips join b6 in db.Boats on s6.BoatId equals b6.BoatId into temp from jn in temp.DefaultIfEmpty() orderby s6.SlipNumber select new { s6, jn }) .ToList() .Select(u => new { SlipID = (int?)u.s6.SlipId, SlipNumber = u.s6.SlipNumber, Length = u.s6.Length, Electricity = u.s6.Electricity, Telephone = u.s6.Telephone, TV = u.s6.TV, BoatDets = jn == null ? new Boat() : new Boat(u.jn.BoatName, u.jn.BoatType, u.jn.BoatOverAllLength), Status = u.s6.Status }) .ToList();notice: BoatDets property, in my two last queries, can not be used in linq to entity, and its readable when your data has been fetched to memory
更多推荐
发布评论