I have recently been learning LINQ queries to the database. I have encountered quite a weird problem. In my query I am trying to compare 2 strings with the Equal() function. I use the ToUpper() function to have one string in upper case and compare it to the same one but in lower case. The Equals() function is sensitive to the cases of letters, which I checked outside of the query, but the Equals() in the query returns the "true" value. It is really a stupid error, but I really checked if it should work like that and if I didn't make a mistake. I write in C#, work in Visual Studio 2022 and connect to the SQLServer database through the Entity Framework. I am going to put my code below:
var wynik = from pracownik in Context.Pracownik
let P = pracownik
let stan = P.stanowisko.ToUpper()
where stan.Equals("informatyk")
select new
{
id_pracownika = P.id_pracownika,
nr_departamentu = P.nr_departamentu
};
foreach (var prac in wynik)
{
Console.WriteLine(prac.id_pracownika " " prac.nr_departamentu);
}
Basically the Equals() function returns true to "informatyk"=="INFORMATYK", but only in the LINQ query. Does someone know what the problem is?
CodePudding user response:
LINQ to Entities doesn't perform the actual comparison. Instead, it translate your statements to SQL (or whichever language your database uses) and lets the database worry it.
Depending on which database that you're using, that SQL will look similar to this:
SELECT id_pracowinka, nr_departamentu
FROM Pracownik
WHERE UPPER(stanow) = "informatyk"
By default, SQL Server use case-insensitive collation. (Many other databases such as Oracle and PostgreSQL are case-sensitive by default.) This can be modified on the tables itself and is probably going to be your easiest way to handle this.
If instead, you'd like LINQ to Entities to render the SQL differently, you'll want to brush up on Expression Trees. https://docs.microsoft.com/en-us/dotnet/csharp/expression-trees
CodePudding user response:
SQL server is by default case insensitive. LINQ converts your C# expression tree to a SQL query with Equals
mapped to ordinary equality checks in SQL Server. As those checks are case insensitive, you get the behavior you see here.