CODEWITHSUNDEEP
asp.netentity-frameworkentity-framework-core
HelloWorld
HelloWorld

Reputation: 656

Is there have any difference between string concat and string interpolation in EF Core?

I use asp.net core 2.1 and EF Core 2. I had called FromSql() to use raw query. But there is something strange result occurred.

var finds = db.JournalInfos.FromSql<JournalInfo>($"SELECT * FROM `journalinfos` WHERE `journalinfos`.`Date` LIKE '{dateKey}%' ORDER BY `Index`").ToList();
var b = finds.Count(); //the count is 0 (It can't count noramlly)

var test = db.JournalInfos.FromSql<JournalInfo>("SELECT * FROM `journalinfos` WHERE `journalinfos`.`Date` LIKE '" + dateKey + "%' ORDER BY `Index`").ToList();
var a = test.Count(); //but in here, the count is normal (1054)

I think there are no any difference between of 2 sql strings.

Watch window shows that the results of 2 sql string values are same

I tried change order those two statements to see if there was a problem with calling FromSql on the same entity over twice, but the result was the same. One uses string interpolation and the other uses + to concatenate strings.

As you can see above, their string.Equal() result was also same. (But use string interpolation case, it doesn't return valid count) But if i use string variables to store each value and pass it to each FromSql parameter, then their query results are same. (valid)

string sql1 = $"SELECT * FROM `journalinfos` WHERE `journalinfos`.`Date` LIKE '{dateKey}%' ORDER BY `Index`";
var finds = db.JournalInfos.FromSql<JournalInfo>(sql1).ToList();
var b = finds.Count(); //Now, it can count normally (1054)

string sql2 = "SELECT * FROM `journalinfos` WHERE `journalinfos`.`Date` LIKE '" + dateKey + "%' ORDER BY `Index`";
var test = db.JournalInfos.FromSql<JournalInfo>(sql2).ToList();
var a = test.Count(); //Also too. (1054)

Are there have any difference between string + concatnation and string interpolation in c# or EF Core?

Upvotes: 1

Views: 440

Answers (1)

Danitechnik
Danitechnik

Reputation: 441

Interpolated strings

Interpolated strings can be translated into a call String.Format(). This happens when you store it in a string variable:

string sql1 = $"...";
var finds = db.JournalInfos.FromSql<JournalInfo>(sql1);

However, if you pass the interpolated string directly (like in your screenshot) it will be of type FormattableString according to the docs and method parameters:

var finds = db.JournalInfos.FromSql<JournalInfo>($"...");

In the second case, the database provider formats your query and escapes dangerous character to avoid SQL injection attacks. If you want to find out the exact difference you have to enable sensitive data logging in EF Core to see the actual queries.

LINQ approach

For this particular problem there might be an easier solution than using raw SQL queries. LINQ extension methods get compiled to SQL, are safe in terms of SQL injection and much easier to maintain if your database model changes.

DateTime date; // This is the same variable like in line 1603 on your screenshot

DateTime minInclusive = new DateTime(date.Year, date.Month, 1);
DateTime maxExclusive = minInclusive.AddMonths(1);

var finds = db.JournalInfos
    .Where(journal => journal.Date >= minInclusive && journal < maxExclusive)
    .OrderBy(journal => journal.Index)
    .ToList();

Upvotes: 1

Related Questions