Entity Framework: String concat in database query

Question

I need to create a string concatenation with an linq statement. I have the following data:

Section

Id: 1, Title: Test1
Id: 2, Title: Test2

Section_User

SectionId: 1, UserId: 1
SectionId: 1, UserId: 2

Users

Id: 1, Name: User1
Id: 2, Name: User2

I need the following result:

SectionId: 1, Users: User1, User2

I create the following Linq statement:

var query2 =    from section in this.context.Sections
                from users in section.Users
                group section by section2.Id into groupedSection
                select new {
                    SectionId = groupedSection.Key,
                    Users = string.Join(",", users.Select (x => x.Name)) // compile error, but I don't know how I write the statement correctly
                };

Could someone tell me, how I can create a string concatenation on database side (not in-memory) with an linq statement.

Thanks!!

I don't think it's supported by EF. So unfortunately the answer is - you can't. — Ivan Stoev
– Ivan Stoev, Commented Jun 25, 2017 at 19:26

Keyur PATEL · Accepted Answer · 2017-06-26 04:17:16Z

0

Based on @Alexei 's link in the comments, your line should look like

var query2 =    from section in this.context.Sections
                from users in section.Users
                group section by section2.Id into groupedSection
                select new {
                    SectionId = groupedSection.Key,
                    Users = string.Join(",", (from u in users select s.Name).ToArray())
                };

However, if that doesn't work due to LINQ to Entities error, and you're willing to simply leave Users as an IEnumerable<string>, you can simply use:

var query2 =    from section in this.context.Sections
                from users in section.Users
                group section by section2.Id into groupedSection
                select new {
                    SectionId = groupedSection.Key,
                    Users = (from u in users select s.Name)
                };

And later whenever needed, you can join the strings, or display the names using a loop.

answered Jun 26, 2017 at 4:17

Keyur PATEL

2,3271 gold badge18 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Schagerl Over a year ago

The answer form Alexei's link does not work for me. Fo this I wrote the following answer: stackoverflow.com/a/44753791/2489386

Keyur PATEL Over a year ago

I've searched for some time now, and there does not seem to by any way for your concat/join operation without ToList() or AsEnumerable(), the closest thing I could think of would be to write a pure MySQL statement and execute it using Raw SQL queries.

Collectives™ on Stack Overflow

Entity Framework: String concat in database query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related