CODEWITHSUNDEEP
c#entity-framework
İsmet Alkan
İsmet Alkan

Reputation: 5447

is there a real difference between db.foo.Where and db.Database.SqlQuery in EntityFramework

Is there a real performance, quality or security difference between the below two approaches?

Approach 1:

ViewBag.Foo= db.Words.Where(w => w.WordBaseID == 1 && w.LanguageID == lang).FirstOrDefault().Text;

Approach 2:

var query = "SELECT Text FROM Words WHERE WordBaseID = {0} AND LanguageID = {1}";
ViewBag.Foo= db.Database.SqlQuery<string>(query, 1, lang).FirstOrDefault();

Upvotes: 0

Views: 145

Answers (2)

Reno
Reno

Reputation: 1319

That will give you the SQL that was generated.

var result = db.Words.Where(w => w.WordBaseID == 1 && w.LanguageID == lang).FirstOrDefault().Text;

var sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

and you can compare diferences.

Upvotes: 0

Darin Dimitrov
Darin Dimitrov

Reputation: 1038710

I don't think that there will be a difference in performance or security. The difference will be in the maintenance of your application. With the second approach you are writing SQL queries in your code -> thus mixing C# and SQL and completely defeating the purpose of an ORM. I mean if you're gonna write plain SQL, consider using ADO.NET -> it will be faster than an ORM.

If you have lots of SQL queries in your code, the day you decide to rename a table you will have lots of places in your code to modify, whereas if you stick to classical ORM tools, you only need to update your mapping and leave the framework worry about emitting the proper SQL queries. Another drawback of writing native SQL queries is that you are now tied to a specific data provider. Remember that not all databases support all SQL methods. ORM frameworks will take into account things like dialects, ...

Upvotes: 5

Related Questions