LINQ to SQL join 3 tables and select multiple columns

Question

0.00/5 (No votes)

See more:

hi i have three tables, Stock, InStock, OutStock

tables columns:

- Stock: IdStock,CodeStock, Date
- InStock: Id_InStock, IdStock, mount_InStock
- OutStock: Id_OutStock, IdStock, mount_OutStock

Table relationship:
- Stock 1:N InStock (FK IdStock)
- Stock 1:N OutStock (FK IdStock)

**Stock sample data:**

IdStock,CodeStock, Date
 - 8 , st10 , 03/12/2014
 - 11 , st20 , 02/12/2014
 - 12 , st25 , 09/12/2014
 - 14 , st27 , 03/12/2014

**InStock sample data**

Id_InStock, IdStock, mount_InStock
- 2, 8, 1250
- 3, 8, 100
- 5, 11, 250

**OutStock sample data**

Id_OutStock, IdStock, mount_OutStock
- 1, 8, 350
- 6, 12, 1100
- 7, 12, 750

i'd like to export all the data (InStock & OutStock) where date of stock between (02/12/2014 & 03/12/2014) in the same gridview

like my gridview column contains:

CodeStock   |  Date           | mount
st10             | 03/12/2014 | 1250
st10             | 03/12/2014 | 100
st20             | 02/12/2014 | 250
st10             | 03/12/2014 | 350

The first three line from InStock table and the last line from OutStock Table

so can you help me about the request should write to give this result and to bind the result in my gridview

Posted 11-Feb-15 23:01pm

Member 11446876

Updated 12-Feb-15 1:11am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Neha Rustagi · Answer 1 · 2015-02-12T20:23:00

Have created a sqlfiddle for same here : http://sqlfiddle.com/#!6/7a99d/11[^]

U can check the sql query there. I tried converting same to linq but as I doesn't have DB on my system, so the tried query might have issues with syntax. Here is the linq query formed based on above fiddle.

C#

(from s in context.stock
join ins in context.InStock
on s.IdStock equals ins.IdStock into Stock_InStock
from subStock in Stock_InStock.DefaultIfEmpty()
where subStock.Date >=StartDate && subStock.Date <=EndDate
select new{s.CodeStock, s.Date, ins.mount_InStock})
.Union
(from s in context.stock
join os in context.OutStock
on s.IdStock equals os.IdStock into Stock_OutStock
from subStock in Stock_OutStock.DefaultIfEmpty()
where subStock.Date >=StartDate && subStock.Date <=EndDate
select new{s.CodeStock, s.Date, os.mount_OutStock});

Please excuse for any syntax errors. You can use the fiddle to form the linq query

LINQ to SQL join 3 tables and select multiple columns

1 solution

Solution 1

Add your solution here

Preview 0

LINQ to SQL join 3 tables and select multiple columns

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us