help with a three table linq query

I have 3 tables that I need to use:

Brand-

BrandID

Name

BrandSource-

BrandID

SourceID

Source-

SourceID

SourceName

Image

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).

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.

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) + 开发者_如何学Python"..." };

And here is how i'm using it:

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>";

    }

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 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

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

}

Sort the LINQ query by BrandID then use a variable to keep track if it is a new brand.

 int lastBrandID = 0;
 string closeHtml = "";
 foreach (var result in join)
    {
        if(result.Brand.BrandID != lastBrandID)
        {
            resultSpan.InnerHtml += closeHtml;
            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>";
            closeHtml = "</td><td>" + result.Brand.DateCreated + "</td><td>" + result.Brand.DateModified + "</td></tr>";
            lastBrandID = result.Brand.BrandID;
        }
        resultSpan.InnerHtml += "<img src='"+result.source+"'></img>";
    }
    resultSpan.InnerHtml += closeHtml;

On a side note, use a StringBuilder instead of concatenating your strings. http://msdn.microsoft.com/en-us/library/2839d5h5%28v=VS.100%29.aspx

You can use GroupBy for this (or group .. by .. into in a query) on your original query thus:

var groups = join.GroupBy(b => b.Brand);

foreach (var group in groups)
{
    var brand = group.Key;
    foreach (var row in group)
    {
       // you get the idea
    }

}