CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sqlc#-4.0
DooDoo
DooDoo

Reputation: 13487

How to return value from 2 tables in one linq query

please consider this table:

PK_Id          Number            Year            Month             Value
-------------------------------------------------------------------------
 1              1                2000              5                 100000
 410            4                2000              6                 10000
 8888           1                2001              5                 100

I Id=8888 and now I want to first select record with Id=8888 and second select previos year of that record*(I mean Id=1)*. How I can do this with linq and one query.

basically we have some queries that first it should find a value from a table (that may be not PK) and find Corresponding records in another tables. How I can do this with linq and one reference to database.

thanks

Upvotes: 0

Views: 2340

Answers (2)

pavanred
pavanred

Reputation: 13823

If I understand your question right, then you need to filter the data of one table and join two tables.

You can join the tables and filter your data 

var query = from c in Table1
            join o in Table2 on c.Col1 equals o.Col2
            where o.Col3 == "x"
            select c;

or you can filter your data from one table and then join the tables (result will be the same)

var query = from c in Table1.Where(item => item.Col3 == "x")
            join o in Table2 on c.Col1 equals o.Col2
            select c;

Upvotes: 1

Markus Jarderot
Markus Jarderot

Reputation: 89231

from a in Record
where a.PK_Id == 8888
from b in Record
where b.Number == a.Number && b.Year == a.Year - 1
select new { Current = a, Previous = b }

or

Record
.Where(a => a.PK_Id == 888)
.SelectMany(a =>
    Record
    .Where(b => b.Number == a.Number && b.Year == a.Year - 1)
    .Select(b => new { Current = a, Previous = b })

Upvotes: 1

Related Questions