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.

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

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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();


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x