Substitute for Group_Concat to customize existing Entity Framework model

Question

I'm using a many-to-many database with join tables connecting main data tables through foreign keys.

http://img12.imageshack.us/img12/6500/databasew.png

With my data representation, it's required to concatenate several rows of subordinate table fields (e.g. Genres.name) into one cell of the databound grid.

Book Title | Genres

Some Book | fiction; horror; mystery

With the dataset I used this SQL query with GROUP_CONCAT for this purpose:

SELECT Books.ID, Books.BOOKTITLE, GROUP_CONCAT(Genres.name, '; ') As Gen

FROM   Books INNER JOIN

       Books_Genres ON Books.ID = Books_Genres.book_id INNER JOIN
       Genres ON Books_Genres.genre_id =Genres.id

GROUP BY Books.ID

As I'm now moving on to EF4 and being pretty new to it, I have no idea how to achieve the same resulting table that could be displayed in a datagrid with entities. What kind of changes should be made to the Entity model to get the same result?

Any help greatly appreciated!

Update:

I have an entity model generated by database (Model1.edmx & Model1.Designer.cs). Do I need to edit the Model1.edmx (xml file)? A few step by step instructions would greatly help. I'm just starting EF4, it's all Greek to me. :)

I made a custom class:

public partial class Book {
        [EdmScalarProperty(EntityKeyProperty = false, IsNullable = true)]
        [DataMember()]
        public global::System.String GenresConcatenated
        {
            get { return string.Join("; ", Genres.Select(g => g.name));}
            set {}
        }
        private List<Genre> Genres{ get; set; }
     }

Now I can access the property GenresConcatenated in IDE IntelliSense, but when I run the application it throws the error:

The number of members in the conceptual type 'MyNamespace.Book' does not match with the number of members on the object side type 'MyNamespace.Common.Book'. Make sure the number of members are the same.

It looks like:

... EntityDataSource does not support custom properties in the partial classes. It only returns the entity that is in the model. (Binding custom property in Entity Framework)

So I'm back to square one. :)

Don't update anything autogenerated. All classes generated by EF are partial. Create your own partial part of `Book` class and add custom property concatenating genres as @Bas showed. — Ladislav Mrnka, Feb 07 '12 at 07:41
Thanks for the tip, Ladislav, will try to do that. Will this new property be available in the datasource table that I try to bind to the datagrid? — Planescaped, Feb 07 '12 at 18:19

score -1 · Accepted Answer · answered Feb 06 '12 at 22:16

-1

How about,

class Book 
{ 
    public int ID { get; set; } 
    public string Title { get; set; } 
    public List<Genre> Genres { get; set; }

    public string Gen
    {
        get
        {
            return string.Join("; ", Genres.Select(g => g.Name));
        }
    }
}

then bind to Book.Gen (or whatever you want to name it)

answered Feb 06 '12 at 22:16

Bas

26,772
8
53
86

I made the property, how do I bind it to Book.Gen? – Planescaped Feb 08 '12 at 00:03
You need to be aware of when/how the data is pulled from the database. If the underlying data is pulled as a separate query (round trip) for each Genre, this solution will perform/scale very badly. The original stored proc is a single round trip to the db to get all data. So it has the optimal performance. – Menace Jan 04 '19 at 15:10

Substitute for Group_Concat to customize existing Entity Framework model

1 Answers1