The problem of full-angle half-width symbols in Entity framework

SQL Server SQL queries are case-insensitive, and LINQ queries are case-sensitive, so when writing LINQ code, it is necessary to note that if this LINQ code is to be parsed into an SQL statement by the Entity Framework (LINQ to Entities), it is not Consider case problems, and if the LINQ code executes in memory, consider the case.

For example, the following LINQ to entities (regardless of capitalization):

Code-Cnblogstagservice _unitofwork.set<tag> (). Where (x => tagnames.contains (x.tagname))

And if it's LINQ, you need to write this (by stringcomparer.ordinalignorecase ignore case):

Content. Tags.removeall (x => tagnames.contains (x.tagname, stringcomparer.ordinalignorecase) = = False);

The problem with this inconsistency is that it's also a matter of writing LINQ, and you have to be different, and you have to consider whether the LINQ code executes in memory or resolves to SQL execution.

This case is well known and is not difficult to solve.

And we've recently had a magical problem with the case problem, which is the same problem with the casing issue-it's not the case for SQL queries in SQL Server to distinguish between Full-width corners, which is differentiated in LINQ.

Here's a real scene from the Cnblogstagservice project (a back-end service that provides a tag service for front-end applications based on the Entity Framework).

Read a LINQ to Entities code First:

Public list<tag> gettags (ienumerable<string> tagnames)
{
    var existedtags = _unitofwork.set< Tag> (). Where (x => tagnames.contains

(x.tagname)). ToList ();
    //...
}

The code above is to query records from the database based on tagname, and then get the corresponding tag entity.

When we encounter a problem, the value of TagNames is {"C + +"}, note that the plus sign here is full angle, the tagname value stored in the database is "C + +" (the plus sign here is a half-width). The result of the above code execution is--existedtags[0]. The value of tagname is "C + +". SQL query can automatically match the whole corner of the corner, then found this is the 1th time to know this, not by exclamation--Good smart SQL Server.

But the inconsistency of this intelligence has led us through a difficult process of troubleshooting.

Then look at the following LINQ code:

var createdtags = tagnames.where (x => existedtags.select (y => y.tagname)
                            . Contains (x, stringcomparer.invariantcultureignorecase) = = 

false)
                            . Select (x => new Tag {TagName = x}). ToList ();

This code is the code for a LINQ query operation in memory, which is used to find tagname that exists in TagNames (type ienumerable<string>) and that Existedtags (the EF entity) does not exist (that is, to find out TagName that do not exist in the database.

According to the previous scenario, the TagNames value is {"C + +"},existedtags[0]. The value of tagname is "C + +". Since this tag already exists in the database, what we expect is no data in the Createdtags, but because LINQ distinguishes the Full-width half-width, the result is--createdtags[0]. The value of tagname is "C + +" and an exception was thrown when savechanges through the Entity Framework:

System.Data.SqlClient.SqlException:Cannot Insert duplicate key row in object ' dbo. Tags ' with 

unique index ' ix_tags_tagname '. The duplicate key value is (c + +).

The purpose of the code here is to create it and save it to the database if the tag of the specified name does not exist in the database. Corresponds to the present scene, has become--"C + +" This tag in the database exist? Database said: exist, called "C + +"; {What is in "C + +}" that is not in {"C +}"}? LINQ said: "C + +"; So, the EF saved "C + +" database, the database said: I have C + +, "C + +" please go away. So there was the anomaly above.

The problem is in the inconsistent behavior of SQL and LINQ. If you do not know the inconsistent situation in advance, there are often the most difficult to deal with bugs! Writing in a blog may seem like a simple question, but when we're obsessed with it, we guess for thousands of reasons, and that's why. Finally found not by sigh-really an adventure!