帮助三表linq查询 [英] help with a three table linq query

问题描述

我有3个表需要使用:
品牌-
BrandID
名称

I have 3 tables that I need to use:
Brand-
BrandID
Name

BrandSource-
BrandID
SourceID

BrandSource-
BrandID
SourceID

来源-
SourceID
SourceName
图片

Source-
SourceID
SourceName
Image

所以我与BrandSource是我的中间表有很多很多关系.我在表格中显示了每个品牌"列，并为源图像​​添加了一个新列.本质上，如果一个品牌有5个来源，我需要在该列中显示该品牌的一行，并在我创建的新列中显示5个不同的来源图片(一个单元格中有5张图片).

So I have a many -many relationship with BrandSource being my intermediate table. I have each Brand column displayed in a table and I made a new column for the source image. Essentially, if there is 5 sources for one brand I need it to show one row for the brand and the 5 different source images in the new column I made.(5 images in one cell).

自从我加入了这三个表以来，它显然会看到BrandSource表中有5行，并在一个单元格中显示每个品牌的5行，并带有单个源图像.

Since I have joined the three tables it obviously sees that there is 5 rows in the BrandSource table and displays 5 rows of each brand with a single source image in a cell.

我确定可以选择不同的品牌，但这仍然不能解决我如何获取每个品牌的所有源图像显示在同一单元格中的问题.

I'm sure I could select distinct brands, but that still doesn't solve my problem of how I can get all of the source images for each brand to show up in the same cell.

这是我的linq代码:(如您所见，此处为简洁起见，此处省略了一些信息).

Here is my linq code:(As you can see there is some info in here that I left out above for brevity).

var join = from b in db.Brands
               join bs in db.Brands_Sources on b.BrandID equals bs.BrandID
               join sb in db.Sources on bs.SourceID equals sb.SourceID
               select new { Brand = b, source = sb.Image, c = b.Description.Length < 204 ? b.Description : b.Description.Substring(0, 204) + "..." };

这是我的使用方式:

foreach (var result in join)
    {
        bool a = result.Brand.Active;
        string chk = string.Empty;
        if (a == true)
            chk = "checked='checked'";
        else
            chk = "";

            resultSpan.InnerHtml += "<tr><td><input type='checkbox' " + chk + "></td><td width='1%'><img width='50px' src='" + result.Brand.Image + "'</img></td>" +
                "<td>" + result.Brand.Name + "</td><td width='60%'>" + result.c + "</td><td><img src='"+result.source+"'></img></td><td>" + result.Brand.DateCreated + "</td><td>" + result.Brand.DateModified + "</td></tr>";

    }

推荐答案

您已经有了一个不错的开始，但是我认为最好不要自己进行三次加入. Linq-to-sql可以为您处理这些细节.如果您离开查询方面一秒钟，然后从所需的结果开始，您会做得更好.据我所知，您想要的对象类型是品牌列表，每个品牌都应包含其来源列表.操作方法如下(从下载 LinqPad 开始)...

You've got a good start, but I think you'd be better served not doing the triple join yourself. Linq-to-sql can handle the details of that for you. If you back away from the query aspect for a second and start with your desired result, you'll do better. From what I can tell, the object type you want out of this is a list of Brands, and each Brand should contain a list of its sources. Here's how you do it (starting with downloading LinqPad)...

// LinqPad C# statement(s)
var results =
from b in Brands
select new {
    Brand = b,
    Sources = (
        from s in Sources
        join xref in BrandSources on s.SourceID equals xref.SourceID
        where xref.BrandID == b.BrandID
        select s
    ).ToList()
};

result.Dump(); // show result in LinqPad

LinqPad显示这是在单个查询中执行的，但是将List<Source>组装到结果对象中的勇气却在幕后.这是LinqPad执行的内容:

LinqPad shows that this executes in a single query, but the guts of assembling your List<Source> in your result object happens behind the scenes. Here's what LinqPad executes:

SELECT [t0].[BrandID], [t0].[Name], [t1].[SourceID], [t1].[SourceName], [t1].[Image], (
    SELECT COUNT(*)
    FROM [Source] AS [t3]
    INNER JOIN [BrandSource] AS [t4] ON [t3].[SourceID] = [t4].[SourceID]
    WHERE [t4].[BrandID] = [t0].[BrandID]
    ) AS [value]
FROM [Brand] AS [t0]
LEFT OUTER JOIN ([Source] AS [t1]
    INNER JOIN [BrandSource] AS [t2] ON [t1].[SourceID] = [t2].[SourceID]) ON [t2].[BrandID] = [t0].[BrandID]

以下是一些针对在家中的人的测试数据:

And here's some test data for those following along at home:

create table Brand (
BrandID int,
Name varchar(50),
)

create table BrandSource (
BrandID int,
SourceID int
)

create table Source (
SourceID int,
SourceName varchar(50),
[Image] varchar(50)
)

insert into Brand select 1, 'Brand1'
insert into Brand select 2, 'Brand2'
insert into Brand select 3, 'Brand3'

insert into Source select 1, 'Source1', 'src1.gif'
insert into Source select 2, 'Source2', 'src2.jpg'
insert into Source select 3, 'Source3', 'src3.bmp'
insert into Source select 4, 'Source4', 'src4.png'
insert into Source select 5, 'Source5', 'src5.raw'

insert into BrandSource select 1, 1
insert into BrandSource select 1, 2
insert into BrandSource select 1, 3
insert into BrandSource select 2, 2
insert into BrandSource select 2, 4

select * from Brand
select * from BrandSource
select * from Source

请注意，您以这种方式获得了#3品牌的空白来源列表，这是我想您想要的.您原来的查询INNER JOIN离开了品牌#3.

Notice that you get an empty list of sources for brand #3 this way, which is what I assume you'd want. Your original query INNER JOINed Brand#3 away.

最后，这是您如何使用查询结果的示例:

Finally, here's an example of how you'd use your query result:

foreach (var result in results) {
   string chk = (result.Brand.Active ? " checked='checked'" : "");
   var buf = new StringBuilder();
   buf.Append("<tr>");
   buf.AppendFormat("<td><input type='checkbox'{0}></td>", chk);
   buf.AppendFormat("<td width='1%'><img width='50px' src='{0}'></img></td>", result.Brand.Image);
   buf.AppendFormat("<td>{0}</td>", result.Brand.Name);
   buf.Append("<td>");

   foreach(var src in result.Sources) {
      buf.AppendFormat("<img src='{0}'></img>", src.Image);
   }

   buf.Append("</td>");
   buf.Append("</tr>");

   resultSpan.InnerHtml = buf.ToString();

}

这篇关于帮助三表linq查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！