How to perform inner join Query in LINQ

Question

I Have three tables and I need to perform inner join query for LINQ using C#. I can go half way but i am very beginner, need help in that many thanks

SQL Query

Select O.CarID, C.customerID, C.Name, C.Address, V.Brand, V.Model, V.Model_Year 
FROM Car_OwnerShip As O, Customers As C, Cars As V
WHERE V.CarID = O.CarID AND O.customerID = C.customerID AND C.Name='toxic';

enter image description here

Take a look at this [answer](http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql) — Daniele, Aug 05 '13 at 09:13
you could have easily found this answer googling. an inner join is the easiest join in linq or sql to do. — dean oliver, Aug 05 '13 at 09:52

score 5 · Answer 1 · edited Aug 05 '13 at 09:55

5

I think that something like this will do the trick:

Car_Ownership
.Join
(
    Customers,
    o=>o.customerID,
    c=>c.customerID,
    (o,c)=>new{o,c}
)
.Join
(
    Cars,
    o=>o.o.CarId,
    v=>v.CarID,
    (o,v)=>new{o,v}
)
.Where
(
    x=>
    x.o.c.Name == "toxic"
)
.Select
(
    x=>
    new
    {
        CarID = x.o.o.CarID,
        customerID = x.o.c.customerID,
        Name = x.o.c.Name,
        Address = x.o.c.Address,
        Brand = x.v.Brand,
        Model = x.v.Model,
        Model_Year = x.v.Model_Year
    }
)

edited Aug 05 '13 at 09:55

Dog Ears

9,637
5
37
54

answered Aug 05 '13 at 09:21

Giannis Paraskevopoulos

18,261
1
49
69

Talk about a verbose query... Very educational, though. – usr Aug 05 '13 at 09:31
@usr I used to hate and not understand Lambdas. They actually had my head nearly explode trying to think how they work. When i got it i just can't think of anything else... – Giannis Paraskevopoulos Aug 05 '13 at 09:35
1

Should that be `(o,v)=>new{o,v}` in the second Join.. – Dog Ears Aug 05 '13 at 09:53
@DogEars Thanks for the Edit...I was blind when i fixed it in the first place. – Giannis Paraskevopoulos Aug 05 '13 at 10:12
@jyparask no worries.. impressive that it was so close without any testing! – Dog Ears Aug 05 '13 at 10:26

score 2 · Answer 2 · answered Aug 05 '13 at 09:24

2

var query = from owner in Car_OwnerShip
            from customer in Customers
            from car in Cars 
            select new { owner, customer, car }
            where owner.CarID = car.CarID and owner.CustomerID = customer.CustomerId and customer.Name = "toxic";

answered Aug 05 '13 at 09:24

lbarczynski

170
11

Although this probably works I would not agree with the where-based join style. – usr Aug 05 '13 at 09:30
it is giving me error! – K.Z Aug 05 '13 at 09:49
its possible because i didn't testing that, what error you got? – lbarczynski Aug 05 '13 at 09:55

score 2 · Answer 3 · answered Aug 05 '13 at 09:25

2

Try this

from o in Car_OwnerShip
join c in Customers on o.customerID equals c.customerID
join v in Cars on o.CarID equals v.CarID 
where c.Name=='toxic'
select new {
O.CarID, C.customerID, C.Name, C.Address, V.Brand, V.Model, V.Model_Year 
}

answered Aug 05 '13 at 09:25

Amit

15,217
8
46
68

How to perform inner join Query in LINQ

SQL Query

3 Answers3