CODEWITHSUNDEEP
c#entity-framework
Karlx Swanovski
Karlx Swanovski

Reputation: 2989

Convert SQL to Entity Framework

How to convert this into entity framework

Emp
______
EmpID
Fname
Lname
PostID

Post
______
PostID
Position
Select e.Fname, e.Lname, p.Position from Emp e inner join Post p
on e.PostID = p.PostID

and bind to DataGridView

Trial:

    var query = db.Employees.Include("Position")        // source
   .Join(db.Positions,         // target
      c => c.PosID,          // FK
      cm => cm.PosID,   // PK
      (c, cm) => new { Employees = c, Positions = cm }) // project result
   .Select(x => x.Employees).Where(m => m.Fname.Contains(txtSrchFirstName.Text) && m.City.Contains(txtSrchCity.Text));
   .Select(x => new { x.EmpID, x.Lname, x.Fname, x.Position, x.City});  // select result

     GridView1.DataSource = query1.ToList();
     GridView1.DataBind();

Upvotes: 1

Views: 106

Answers (2)

Martin Booth
Martin Booth

Reputation: 8595

jmcihinney's query is equivalent to your sql query however normally, with EF, you'd try to use navigation properties to access related data so that you don't have to describe the relationships between entities in each and every query.

You only need to write

var query = from e in db.Employees
            from p in e.Posts
            select new { e.EmpID, e.Lname, e.Fname, p.Position, p.City});

NB

I don't know where City comes from, it's not in your original query but it is in your trial, and secondly, this requires the relationship to be correctly defined (with navigation property) in the model.

Upvotes: 0

jmcilhinney
jmcilhinney

Reputation: 54417

From off the top of my head:

var query1 = from e in Emp
             join p in Post
             on e.PostID equals p.PostID
             select new {e.Fname, e.Lname, p.Position};

Upvotes: 1

Related Questions