Strange collation issue using LINQ to SQL
I have the strangest collation error in LINQ-SQL. I have this query that performs a left-outer join on 2 parameters. From the query, I want to extract a column for the left joined table if it's NOT null, and otherwise from the first table. The field in question is a char, and both table have the same collation. The LINQ code is show below, and I have put in bold the line that is causing the error.
from contentList in dc.ContentList
join portalPrice开发者_开发知识库Class in dc.PortalContentPriceClass
on contentList.ContentID equals portalPriceClass.ContentID
into ppc
from portalSpecificPriceClass in ppc.Where(portalPriceClass =>
portalPriceClass.PortalID==portalId).DefaultIfEmpty()
where contentListPriority.PortalID == portalId
select new
{
ID = content.ID,
PriceClass = (portalSpecificPriceClass == null) ? contentGame.PriceClass : portalSpecificPriceClass.PriceClass
};
Unfortunately, I get the following error, and cannot find anything to account for it:
System.Data.SqlClient.SqlException: Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.
Could you please check your table with sp_help to see if there is a different collation in the columns.
There is also a very through explanation on that issue here
You could make the query a bit easier, and avoid the issue, and I think this should "work":
from a in dc.ContentList
where a.ContentID != null
select new { ID = a.ContentID, a.PriceClass, .. }).Union(
from b in dc.ContentList
where dc.ContentList.FirstOrDefault(a => a.ContentID == b.ContentID) == null)
select new { ID = b.ContentID, b.PriceClass, .. });
精彩评论