LINQ subquery returns brackets and fieldnames, how to get just the value?

Question

My LINQ query is the following, I query two tables, Settlements and Bills which have a one to many relationship, in particular there can be one settlement with one or more bills.

var TheSettlements = from settlement in context_.Settlements
                                 select new
                                 {
                                     asettlementid = settlement.SettlementId,
                                     SqBills = string.Join(",",
                                     (
                                     from b in context_.Bills
                                     .Where(b => b.SettlementId == settlement.SettlementId)
                                     select new { b.BillMunicipalityId }
                                     ))
                                 };

Now the trivial part (for me), is that I would like the bills concatenated, so after many hours of trial and error, I got my results but the Bills(BillMunicipalityId) are presented inside brackets including the fieldname, like this.

The way I export the data, to a txt to be more precise, is this.

foreach (var settlement in TheSettlements)
            {
                SettlementsText
                    .Append(settlement.asettlementid).Append(Delimiter)
                    .Append(settlement.SqBills.ToString()).Append(Delimiter)
                    .Append(Newline);
            }

And the results I get in the txt.

3,{ BillMunicipalityId = f9e47f81-fc97-4008-b93d-d384230c53aa },
6,,
7,{ BillMunicipalityId = 8b66610a-20c1-4f47-9f37-489d1a8ce31a },{ BillMunicipalityId = 003d59d4-7bcb-4603-b42c-dc389dd8fb06 },{ BillMunicipalityId = 0070bb29-e3a1-4317-b5e2-3d1ef08dd20b },

How should I handle this to get only the values?
Just the GUID of every BillMunicipalityId, without the { BillMunicipalityId = } part.

Check this question https://stackoverflow.com/q/4765084/2659549 — nicusor, Nov 08 '17 at 20:55
Taking a look with debuger, I see that both the results of the first query are returned inside brackets and with fieldnames, however the way I chose to "print" them doesn't "print" the brackets of the main query results. Let me edit the question to include the way I export the data. — Christos Karapapas, Nov 08 '17 at 21:01

score 2 · Accepted Answer · answered Nov 08 '17 at 21:04

I think rather than selecting as a new object you could just select the value like this:

var TheSettlements = from settlement in context_.Settlements
                     select new
                     {
                         asettlementid = settlement.SettlementId,
                         SqBills = string.Join(",",
                         (
                         from b in context_.Bills
                         .Where(b => b.SettlementId == settlement.SettlementId)
                         select b.BillMunicipalityId
                         ))
                     };

I can't believe it was really that simple! Thank you! – Christos Karapapas Nov 08 '17 at 21:07 — Christos Karapapas, Nov 08 '17 at 21:07

score 2 · Answer 2 · answered Nov 08 '17 at 21:11

Your Linq statement looks really strange to me. As it shows in the question you're mixing Linq with extensions methods.

If context is a DbContext which is going to the database, concating the results with string.Join won't work as this statement can't be translated to SQL code. If context however contains in memory data this may work. I advise however to not use string.Join within Linq unless you add a clear comment to the code, this Linq should never hit the database.

When this code will hit the database, you'll get an NotSupportedException with the message:

LINQ to Entities does not recognize the method 'System.String Join[Int32]'

The second thing I notice in your query, normally the one-to-many relation is known by the datamodel and you shouldn't need to join the results yourself.

The easiest way to solve this, is to use an intermediate query, which gets the results from the database and after running the query and getting the data into memory, perform the conversion with string.Join()

This would look like:

var TheSettlements =
    from settlement in context_.Settlements
    select new
    {
        asettlementid = settlement.SettlementId,
        SqBills = (
            from b in settlement.Bills
            select b.BillMunicipalityId
            ).ToList(),
    };

// Get the results in memory:
var results = TheSettlements.ToArray();

// Format the results:
var printResults = results.Select(s => 
      s.asettlementid.ToString() + ", " + string.Join(", ",s.SqBills));

Thank you for the advice! That is a much cleaner approach. – Christos Karapapas Nov 08 '17 at 21:28 — Christos Karapapas, Nov 08 '17 at 21:28

LINQ subquery returns brackets and fieldnames, how to get just the value?

2 Answers2