Join multiple tables and single output using Entity Framework mvc5

Question

I am very new to linq query so I need to join multiple tables and single output using Entity Framework mvc5

Below is my 3 table structures and Table_Application is a main table

1) Table_Application

Id   ApplicationName    ServiceId     ProductID

1         Myapp           1               1

2) Table_Service

ServiceId     SName
  1             S1

3) Table_Product

ProductID     PName
  1             P1

I need linq out result data in linq list base on Table_Application ID

ApplicationName   SName   PName
 Myapp             S1      P1

my sql query some thing like that

select t1.ApplicationName,t2.SName,t3.PName from Table_Application t1,Table_Service t2,Table_Product t3 where t1.ServiceId =t2.ServiceId  and t1.ProductID=t3.ProductID and t1.Id="mysessionid"

I tried for single table coming but not able to join Table_Service and Table_Product

  var IA = db.Applications
      .Where(x => x.ID == Id)
      .Select(IAview => new IAViewModel 
      {
          ApplicationName = IAview.ApplicationName


      }).ToList();

my table structure some other fields shall i post what i tried? and i explain in simple language. @jeroenh — mazhar, Aug 24 '18 at 08:29
Ideally you post the C# code you have tried and explain where you are stuck — jeroenh, Aug 24 '18 at 08:32
I have edited my question please check and confirm. @jeroenh — mazhar, Aug 24 '18 at 08:38
Possible duplicate of [What is the syntax for an inner join in LINQ to SQL?](https://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql) — jeroenh, Aug 24 '18 at 08:41
I suggest you look at similar questions that have been posted on stack overflow, as well as the [documentation](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause) — jeroenh, Aug 24 '18 at 08:42

Gareth Lewis · Accepted Answer · 2018-08-24T12:14:46.713

4

Take a look at the LINQ query syntax - it's similar to SQL so can be easier to understand if you're coming from that sort of background.

There's a tutorial on http://www.tutorialsteacher.com/linq/linq-query-syntax which will give you a basic starting point, then you can just add in joins etc as required.

Note that the following is untested and will give you a list of IAViewModel objects, but I think you would want something similar to the following:

var a = (from app in db.Applications
join service in db.Services on app.ServiceId equals service.ServiceId
join product in sb.Products on app.ProductId equals product.ProductId
where app.Id == session_id
select new IAViewModel
{
    ApplicationName = app.ApplicationName,
    ServiceName = service.SName,
    ProductName = product.PName
}).ToList();

If you only want a single result out of it, you can leave out the .ToList() and instead use something like .FirstOrDefault()

edited Aug 24 '18 at 12:14

answered Aug 24 '18 at 08:53

Gareth Lewis

76
3

how to pass session value. @Gareth Lewis – mazhar Aug 24 '18 at 08:54
@mazhar I've updated my answer to include the where clause – Gareth Lewis Aug 24 '18 at 09:05
I want to assing this view model please check my question your answer given correct list but want to assign like this .Select(IAview => new IAViewModel. @Gareth Lewis – mazhar Aug 24 '18 at 09:46
@mazhar I've updated my answer to show how you can select a new IAViewModel from the LINQ query – Gareth Lewis Aug 24 '18 at 12:16

Dilip · Answer 2 · 2018-08-24T08:39:57.573

2

 var T = db.Table_Application.Join(
                    db.Table_Service,
                    TA=> TA.ServiceId,
                    TS => TA.ServiceId,
                    (TA,TS ) => new { TA, TS }
                    ).Join(
                    db.Table_Product,
                    TA2=> TA2.TA.ProductID,
                    TP => TP.ProductID,
                    (TA2, TP) => new { TA2, TP }
                    ).Where(c => c.TA.Id == "mysessionid").FirstOrDefault();

edited Aug 24 '18 at 08:39

answered Aug 24 '18 at 08:35

Dilip

21
2

I will check and get backup your answer. @Dilip – mazhar Aug 24 '18 at 08:39
how the name will select t1.ApplicationName,t2.SName,t3.PName. @Dilip – mazhar Aug 24 '18 at 08:40
FirstOrDefault() will select all the columns, or you can use Linq Select to filter columns @mazhar – Dilip Aug 24 '18 at 08:41

Join multiple tables and single output using Entity Framework mvc5

2 Answers2