c# - with - linq join where clause lambda




What is the syntax for an inner join in LINQ to SQL? (12)

Actually, often it is better not to join, in linq that is. When there are navigation properties a very succinct way to write your linq statement is:

from dealer in db.Dealers
from contact in dealer.DealerContacts
select new { whatever you need from dealer or contact }

It translates to a where clause:

SELECT <columns>
FROM Dealer, DealerContact
WHERE Dealer.DealerID = DealerContact.DealerID

I'm writing a LINQ to SQL statement, and I'm after the standard syntax for a normal inner join with an ON clause in C#.

How do you represent the following in LINQ to SQL:

select DealerContact.*
from Dealer 
inner join DealerContact on Dealer.DealerID = DealerContact.DealerID

And because I prefer the expression chain syntax, here is how you do it with that:

var dealerContracts = DealerContact.Join(Dealer, 
                                 contact => contact.DealerId,
                                 dealer => dealer.DealerId,
                                 (contact, dealer) => contact);

It goes something like:

from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}

It would be nice to have sensible names and fields for your tables for a better example. :)

Update

I think for your query this might be more appropriate:

var dealercontacts = from contact in DealerContact
                     join dealer in Dealer on contact.DealerId equals dealer.ID
                     select contact;

Since you are looking for the contacts, not the dealers.


One Best example

Table Names : TBL_Emp and TBL_Dep

var result = from emp in TBL_Emp join dep in TBL_Dep on emp.id=dep.id
select new
{
 emp.Name;
 emp.Address
 dep.Department_Name
}


foreach(char item in result)
 { // to do}

Try this :

     var data =(from t1 in dataContext.Table1 join 
                 t2 in dataContext.Table2 on 
                 t1.field equals t2.field 
                 orderby t1.Id select t1).ToList(); 

Use LINQ joins to perform Inner Join.

var employeeInfo = from emp in db.Employees
                   join dept in db.Departments
                   on emp.Eid equals dept.Eid 
                   select new
                   {
                    emp.Ename,
                    dept.Dname,
                    emp.Elocation
                   };

You create a foreign key, and LINQ-to-SQL creates navigation properties for you. Each Dealer will then have a collection of DealerContacts which you can select, filter, and manipulate.

from contact in dealer.DealerContacts select contact

or

context.Dealers.Select(d => d.DealerContacts)

If you're not using navigation properties, you're missing out one of the main benefits on LINQ-to-SQL - the part that maps the object graph.


basically LINQ join operator provides no benefit for SQL. I.e. the following query

var r = from dealer in db.Dealers
   from contact in db.DealerContact
   where dealer.DealerID == contact.DealerID
   select dealerContact;

will result in INNER JOIN in SQL

join is useful for IEnumerable<> because it is more efficient:

from contact in db.DealerContact  

clause would be re-executed for every dealer But for IQueryable<> it is not the case. Also join is less flexible.


OperationDataContext odDataContext = new OperationDataContext();    
        var studentInfo = from student in odDataContext.STUDENTs
                          join course in odDataContext.COURSEs
                          on student.course_id equals course.course_id
                          select new { student.student_name, student.student_city, course.course_name, course.course_desc };

Where student and course tables have primary key and foreign key relationship


var Data= (from dealer in Dealer join dealercontact in DealerContact on dealer.ID equals dealercontact.DealerID
select new{
dealer.Id,
dealercontact.ContactName

}).ToList();

var list = (from u in db.Users join c in db.Customers on u.CustomerId equals c.CustomerId where u.Username == username
   select new {u.UserId, u.CustomerId, u.ClientId, u.RoleId, u.Username, u.Email, u.Password, u.Salt, u.Hint1, u.Hint2, u.Hint3, u.Locked, u.Active,c.ProfilePic}).First();

Write table names you want, and initialize the select to get the result of fields.


var q=(from pd in dataContext.tblProducts join od in dataContext.tblOrders on pd.ProductID equals od.ProductID orderby od.OrderID select new { od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 }).ToList(); 




join