这让我疯了!
我尝试了很多方法(以及每种方式的多次迭代)但得到了相同的错误。
我在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
更多推荐
发布评论