how to convert SQL join query to Entity Framework in C#?


I have this SQL query

select o.id, o.name , k.image
from dbo.Office as o
left join dbo.Rate as r on o.id = r.officeid
left join dbo.Splash as k on k.officeid = o.id
group by o.id, o.name, k.image
order by avg(r.value) desc

now, I would like to convert it to Entity framework syntax.

How to achieve this?


Asked by:- Eng MahmoudEl Torri
2
: 4932 At:- 8/10/2017 7:50:31 AM
Asp.net Entity Framework

Can you paste more details about your questions, like images of relationship of database, required output table sample(if possible)? 0
By : vikas_jk - at :- 8/10/2017 8:23:17 AM






2 Answers
profileImage Answered by:- neena

You can try this command using Entity framework (.edmx model), i suppose you have created .edmx and mapped your database with the application(this is half solution)

using (var ctx = new EdmxContext())
        {
            var query = ctx.Office
                .Include(a=>a.Rate)// Your Rate table id is linked with Office table using reltionship
                .Include(a=>a.Splash) // Your Rate table id is linked with Office table using reltionship
                .GroupBy(a=>a.Office.id).OrderByDescending(a=>a.Value).ToList(); 

            

        }

Above is half solution, to properly check your page table details, please provide database relationship image, with example data, i test it here and try to give bettter reply

Or check these links for taking out Average

Another link of average example

For reference of EF

1
At:- 8/10/2017 8:40:29 AM Updated at:- 8/10/2017 8:43:18 AM


profileImage Answered by:- pika

You can run your query using Entity framework also, with the help of .SQLQuery() method, something like this

  var query = "SELECT * FROM table_name";
  var queryResult=Context.Table_Name.SqlQuery(query).ToList();

it may help you.

1
At:- 8/11/2017 11:47:55 AM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use