Questions › how concatenate multiple rows in LINQ with two tables?

I try to concatenate multiple rows in a single row with 2 tables which one of them has a foreing key

I have this tables:

Table Printer:

PrinterID    Name      Description 
--------------------------------
1          Printer1   Description1
2          Printer2   Description2

Table Resolution:

ResolutionID    Measure    PrinterId
--------------------------------
1              123            1
2              234            1
3              345            2
4              456            2

I need this in the GridView:

PrinterID          Name              Description          Resolution
------------------------------------------------------------------------
1                 Printer1           Description1          123, 234
2                 Printer2           Description2          345, 456

I have this code but I am stuck

    var Printer = from tPrinter in Context.Printer
                  join tResolution in Context.Resolution on tPrinter.PrinterId equals tResolution.PrinterId into collection2
                  from subcase2 in collection2.DefaultIfEmpty()
                  where tPrinter.DisableDate == null

                  select new
                  {
                      tPrinterPrinterId = tPrinter.PrinterId,
                      tPrinterName = tPrinter.Name,
                      tPrinterDescription = tPrinter.Description,
                      tCountryName = tPrinter.City1.State.Country.Name,
                      tStateName = tPrinter.City1.State.Name,
                      tCityName = tPrinter.City1.Name
                  };

  return Printer
Comments :
M. Schena replied

shouldn't it be tprinter.PrinterId ? in line: on Printer.PrinterId equals tRe...

yes you are right, sorry for the error

M. Schena replied

so it's working now?

M. Schena replied

do you use EntityFramework? and how do you open your Context?

Yes I do, and the conecction is not the problem, the Entity works with all the rest of querys


3 Answers :
bradlis7 answered

A join creates a row for each combination of printer and resolution. I think you want to select it in a subquery.

var Printer = from tPrinter in Context.Printer
              where tPrinter.DisableDate == null

              select new
              {
                  tPrinterPrinterId = tPrinter.PrinterId,
                  tPrinterName = tPrinter.Name,
                  tPrinterDescription = tPrinter.Description,
                  tCountryName = tPrinter.City1.State.Country.Name,
                  tStateName = tPrinter.City1.State.Name,
                  tCityName = tPrinter.City1.Name,
                  resolution = Context.Resolution.Where(r => r.PrinterId == tPrinter.PrinterId)
              };

return Printer;

Once you've done a ToList() to get it out of the database, you can then String.Join(", ", printer.resolution) to create a comma delimited list.

If you are using LINQ to Entities this String.Join won't work because EF doesn't know how to translate that in SQL query. You must first fetch the results by using a ToList() or better Take(10) and then apply the String.Join.;
bradlis7 replied
@MihailStancescu you're right, thanks for reminding me. I've updated the answer.;
Abraham Rivera replied
soyyy, but does not work with: resolution = String.Join(", ", Context.Resolution.Where(r => r.PrinterId == tPrinter.PrinterId).ToList());
Abraham Rivera replied
Somebody hel me pls xD;
bradlis7 replied
You're actually still inside of an LINQ-to-SQL call that's trying to create an SQL call. It doesn't understand String.Join, nor ToList(). You'll have to get the information as a list of values in the query, and then, after running Printer.ToList(), you can then do String.Join(", ", p.resolutions). You can also run ToList() and then use another .Select() statement to return resolutions in the format you want.;
Sahi answered
var Printer = Context.Printer.Join(Context.Resolution, p => p.PrinterID, r => r.PrinterId, (p, r) => new
        {
            PrinterId = p.PrinterID,
            Name=p.Name,
            Description=p.Description,
            Resolution = String.Join(", ", Context.Resolution.Where(k => k.PrinterId == p.PrinterID).Select(lm => lm.Measure.ToString()))
        }).Distinct();
michaela112358 answered

This may not be exactly what you were asking but you could display the data that way without any complicated linq if you are using model binding. This works because the model should know that a Printer has a collection of Resolutions associated with it (from the foreign key relationship).

<asp:GridView ID="GridView1" runat="server" ItemType="Business.Models.Printer" SelectMethod="SelectPrinters" Style="margin-left: 30px" AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="PrinterID" HeaderText="Id"/>
        <asp:TemplateField HeaderText="Resolution">
            <ItemTemplate>
                <asp:DataList ID="Resolutions"
                    RepeatDirection="Horizontal"
                    RepeatLayout="Table"
                    RepeatColumns="0" runat="server" ItemType="Business.Models.Resolution" DataKeyNames="ResolutionID" DataSource='<%# Item.Resolutions %>' >
                    <ItemTemplate>
                        <asp:Label ID="lblResolutions" runat="server"> <%#Item.Resolution.Measure %></asp:Label>
                    </ItemTemplate>

                    <SeparatorTemplate>
                        ,
                    </SeparatorTemplate>

                </asp:DataList>

            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Select Method:

    public IQueryable<Printers> SelectPrinters()
    {
        var query = dbContext.Printers;
        return query;
    }