WCF/WF - how to join 3 tables using linq to sql query in wcf function

Asked By skumari woonna on 20-May-10 01:52 AM
end of post
Mash B replied to skumari woonna on 20-May-10 02:02 AM

LINQ To SQL or just plain LINQ allows us to do a join on multiple conditions using an elegant technique. will show you how to perform a Join on multiple conditions. As an example I will take two tables called House and ShoppingMall.

image

Both these tables have PostCode and CouncilCode as common fields. Lets say that we want to retrieve all records from ShoppingMall where both PostCode and CouncilCode on House match. This requires us to do a join using two columns. In LINQ such a join can be done using anonymous types. Here is an example.

var query = from s in context.ShoppingMalls
            join h in context.Houses
            on
            new { s.CouncilCode, s.PostCode }
            equals
             new { h.CouncilCode, h.PostCode }
            select s;

The code above gets translated into this SQL query.

SELECT [t0].[ShoppingMallId], [t0].[Address],
[t0].[PostCode], [t0].[CouncilCode]
FROM [dbo].[ShoppingMall] AS [t0]
INNER JOIN [dbo].[House] AS [t1]
ON ([t0].[CouncilCode] = [t1].[CouncilCode])
AND ([t0].[PostCode] = [t1].[PostCode])

And when the above query is executed it produces the results we want.

Anoop S replied to skumari woonna on 20-May-10 02:03 AM
refer this example for joining myltiple table in LINQ
http://ericfickes.com/2010/02/how-to-join-two-tables-using-linq-to-sql/
Goniey N (Mr. G) replied to skumari woonna on 20-May-10 09:46 AM


Retrieving data from Customers, Orders, and Orders_Details table with a single query.


public void ShowOrders02()
{
	var query = from c in db.Customers
		where c.Country == "USA"
		select new
		{                                   
			c.CompanyName,                                   
			Orders = from o in c.Orders                                            
			select new
			{                                                       
				o.ShipCity,
				details = from d in o.Order_Details
				where d.UnitPrice < 20
				select new
				{
					d.ProductID, d.UnitPrice
				}
			}
		};
	ObjectDumper.Write(query, 2);
}



//I Hope This Will Help You To Solve Your Problem.......