[Linq To SQL] solves Collation conflicts during join operations.

[Linq To SQL] solves Collation conflicts during join operations.

Background

Now two tables

A:

 

B:

 

If you try the join operation, an error is returned:

 

Analysis

Because the sorting rules of the two table fields are different, the table cannot be compared during connection, and an exception is thrown. If you use an SQL statement, you only need to specify Collation for display (). After reading this article, EF has not provided a reference for explicitly specifying the Collation method in the code. Currently, the workarounds that I can think of are as follows (it is impossible to change the user's database settings ):

1 Use Cross Join. (From a in tablea from B in tableb where a. Key = B. Key ...)

2 use the select... where id in (select...) Method

3. compute the fields to be connected (varchar), such as substring, to see if it can change its nature.

4. Convert the fields to be connected (if all the fields are encoded, convert them to the int type) and compare them to see if they can bypass the sorting rules.

5. Use the ef api ExecuteQuery method to directly use SQL statements for queries

6. If it doesn't work, you can only retrieve all the data first and then process it locally.

 

Solution

Methods 1-3 cannot bypass the sorting rules.

Method 4:To SQLTo Convert string to int (the business field may be a serial number composed of pure numbers), use Convert. toInt32 is not supported (although it is supported in Linq Pad, It is not supported in ). You must use methods such as SqlFunctions or EntityFunctions.

Unfortunately, I have not found any method in these APIs to convert string cast to int. Fortunately, sqlFunctions provides a CheckSum method to calculate string or other types of hash values. Are two strings equal and reliable based on the same hash value? The answer is: relatively reliable.

Try it. Good:

 

Remarks

It seems that sqlserver 2012 solves this inconvenience. If you are interested, try it.

How to perform left join in Linq to SQL

You can use the into syntax and the DefaultIfEmpty () method to load null values in the right table. Example: var query = from r in db. rolesjoin sr in (from ss in db. staffInRoleswhere ss. staffId = staffIdselect ss) on r. id equals sr. roleIdinto xfrom y in x. defaultIfEmpty () select new RoleInfo {RoleId = r. id, RoleName = r. name, IsSet = y! = Null}; note that you can use y at the end! = Null to determine whether the right value is null. The corresponding SQL generated by this Linq to SQL statement is exec sp_executesql n' SELECT [t0]. [Id] AS [RoleId], [t0]. [Name] AS [RoleName], (CASEWHEN [t2]. [test] is not null then 1 ELSE 0END) AS [IsSet] FROM [Role] AS [t0] left outer join (SELECT 1 AS [test], [t1]. [StaffId], [t1]. [RoleId] FROM [StaffInRole] AS [t1] China self-learning Programming Network Author: role name

How to perform left join in Linq to SQL

}; Note that y can be used at the end of this article! = Null to determine whether the right value is null. The corresponding SQL generated by this Linq to SQL statement is exec sp_executesql n' SELECT [t0]. [Id] AS [RoleId], [t0]. [Name] AS [RoleName],
(CASEWHEN [t2]. [test] is not null then 1 ELSE 0END) AS [IsSet]
FROM [Role] AS [t0]
Left outer join (
SELECT 1 AS [test], [t1]. [StaffId], [t1]. [RoleId]
FROM [StaffInRole] AS [t1]
) AS [t2] ON ([t0]. [Id] = [t2]. [RoleId]) AND ([t2]. [StaffId] = @ p0) ', n' @ p0 int', @ p0 = 1
Some articles on this site are from the Internet and copyright belongs to the original author.
For more information, see SQL Server programming in Visual C #. Use APIs to obtain the list of available SqlServer servers in the LAN. C # decompile Microsoft MSDN2003 help documents, and save the decompilation results to an SQLSERVER database. SQL server Stored Procedures and C # paging class simplify your code SQLServer2000 Data Access Base class.