我快疯了!
我尝试了许多方法(每种方法的许多迭代)，但得到相同的错误。
我已经在LINQPad中尝试了查询并得到了所需的结果。
场景:码头。我要一份所有船票的清单，上面有船的详细信息，如果有船登记在船票上。如果在滑条上没有注册的船，那么boatID字段可能是NULL(我知道这应该设置为一个键，但我试图使用Linq在不改变数据库的情况下得到答案)。有一个"滑动"表，其中包含滑动列表，包括BoatId字段(用于船只在滑动处注册时)。第二个表是'Boat'表，boid作为键和其他船的详细信息。
这是一个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基本类型或枚举类型。
我已经在这个网站(和其他网站)上深入研究了尽可能多的解决方案，但我似乎在做正确的事情。
根据你的评论，问题是你从BoatName, BoatType and BoatOverAllLength尝试create a string，你不能在linq to entities中格式化字符串，正如我之前所说的(之前的帖子，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中使用，当您的数据已被提取到内存时，其可读
1-在.netframework4你不能使用enumerations在linq实体，但在.netframework4.5你可以，并在.netframework4当你使用enumeration这个异常发生，虽然我没有看到任何enumeration在你的代码…
2-你可以在你的select属性注释一个一个，找到导致异常的属性，并检查它的类型…
3-可能你不会有任何问题，如果获取数据之前选择他们作为literal像这样:
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.BoatName + " [" + u.jn.BoatType + ", " + u.jn.BoatOverAllLength + "]",
Status = u.s6.Status
})
.ToList();
4-小心，如果jn为空，这行BoatDets = u.jn.BoatName + " [" + u.jn.BoatType + ", " + u.jn.BoatOverAllLength + "]",会导致异常