CODEWITHSUNDEEP
c#.netlinqlinq-to-sqlcollation
Steve Kiss
Steve Kiss

Reputation: 1118

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 portalPriceClass 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.

Upvotes: 4

Views: 1454

Answers (2)

ravndal
ravndal

Reputation: 225

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, .. });

Upvotes: 0

Ernesto
Ernesto

Reputation: 1592

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

Upvotes: 1

Related Questions