Strange Mapping Problem Using LINQ to SQL

Question

I have the strangest mapping error in LINQ-SQL. I have this query that performs a left outer join in 2 parameters. From the query, I want to extract a column for the left joined table if it is NOT null, otherwise - from the first table. The field in question is char , and both tables have the same sort. The LINQ code is below and I am highlighted in bold which causes an 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: It is not possible to implicitly convert a char value to a char because sorting of the value is not allowed due to the sorting conflict.

Answer 1

Could you check your table with sp_help to see if the columns have different sorting.

This release also has a very detailed explanation here.

Answer 2

You can make the request a little simpler and avoid the problem, 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, .. });