How to write entity framework query and Linq query on c#?

4055 views c#
-2

enter image description here

Hi, I have this table. I want to write this query in my controller.

select sum(Amount) from tbl_Amounts where fk_Roleid=2

which shows result of 800 in sql. I don't know how to write it in both EF and Linq. If someone could kindly show me the way to write in both ways would be really helpfull.

answered question

You can use Database.SqlQuery<TResult> in EF to execute raw SQL queries like that. Which MVC & EF version you're using?

EF Version=4.0.0.0 and Visual studio version 2012.

2 Answers

5

This should basically be the Linq syntax for your request.

Linq is some "internal framework" of .NET / C#, and you use Entity framework.

There is no real meaning in separating the two, in my opinion.

This is the "Linq to SQL" syntax I would use on the "DataContext" that you cretate with EF :

DataContext
    .tbl_Amounts
    .Where(a => a.fk_Roleid = 2)
    .Sum();

If you have a collection of record called Amounts, this would be the "linq to objects" version :

Amounts
    .Where(a => a.fk_Roleid = 2)
    .Sum();

As you can see, there is no real difference ! (excepted that, under the hood, the first one on an EF Data Context will be converted to a SQL db request)

But it could actually depends on your Entity Framework version (not all linq features are correctly translated to SQL)

posted this
7

There are 2 ways to execute SQL query in EF:

1) Using LINQ

using (var db = new DataContext())
{
    int amount = (from am in db.tbl_Amounts where am.fk_Roleid == 2 select am.amount).Sum();
}

2) Using Database.SqlQuery<T>()

using (var db = new DataContext())
{
    string query = "select sum(Amount) from tbl_Amounts where fk_Roleid=2";
    int amount = db.Database.SqlQuery<int>(query).Single(); // or First()
}

Note that if you're using EF Core, Database.SqlQuery<T> doesn't exist and you should use Execute raw SQL query in EF Core.

posted this

Have an answer?

JD

Please login first before posting an answer.