LINQ to SQL连接3个表并选择多个列 [英] LINQ to SQL join 3 tables and select multiple columns

问题描述

您好我有三张桌子，Stock，InStock，OutStock



表格栏目：

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

表关系：

- 股票1：N InStock（FK IdStock）

- 股票1：N OutStock（FK IdStock）



**股票样本数据：**

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样本数据**

**InStock sample data**

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

** OutStock样本数据**

**OutStock sample data**

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

我想输出所有数据（InStock& OutStock）在同一网格视图中的（02/12/2014& 03/12/2014）之间的库存日期



像我的gridview列包含：

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

来自 InStock 表格的前三行以及 OutStock 表



所以你能帮我解决一下这个请求应该写的结果并将结果绑定到我的gridview

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

推荐答案

在这里创建了一个sqlfiddle： http ：//sqlfiddle.com/#！6 / 7a99d / 11 [ ^ ]



你可以检查那里的sql查询。我尝试将其转换为linq但由于我的系统上没有DB，因此尝试过的查询可能会出现语法问题。这是基于上述小提琴形成的linq查询。

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.

(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});

请原谅任何语法错误。您可以使用小提琴来形成linq查询

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

这篇关于LINQ to SQL连接3个表并选择多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！