Entity Framework 4.1 Code-First approach to realize many-to-many relation over Domain-Services -

i had problems while creating database model using newest entity framework , code-first (see entity framework 4.1 code first approach create many-to-many relation details).

meanwhile i've figured out problem isn't entity framework more, using along wcf ria domainservices.

for sake of completeness - that's relevant code-first code:

// // models //  public class author {     public author()     {         this.books = new collection<book>();     }      [databasegenerated(databasegeneratedoption.identity)]     [key]     public int id { get; set; }      [maxlength(32)]     [required]     public string name { get; set; }      [include]     [association("author_book", "id", "id")]     public collection<book> books { get; set; } }  public class book {     public book()     {         // this.authors = new collection<author>();     }      [databasegenerated(databasegeneratedoption.identity)]     [key]     public int id { get; set; }      [maxlength(32)]     [required]     public string name { get; set; }      // create navigation property, there seems no way     // tell domainservice include it.     // public collection<author> authors { get; set; } }   // // mappings //  public class authormapping : entitytypeconfiguration<author> {     public authormapping()         : base()     {         this.hasmany (g => g.books)             .withmany(/*m => m.authors*/)             .map     (gm => gm.totable("author_book"));     } }   // // dbcontext //  public class bookauthormodelcontext : dbcontext {     public bookauthormodelcontext()         : base(@"data source=localhost\mssqlserver2008r2;database=bookauthor;integrated security=true;")     {     }       public dbset<author> authors  { get; set; }     public dbset<book>   books { get; set; }       protected override void onmodelcreating(dbmodelbuilder modelbuilder)     {         modelbuilder.configurations.add(new authormapping());         modelbuilder.conventions.remove<pluralizingtablenameconvention>();     } }   // // domainservice //  [enableclientaccess()] public class bookauthordomainservice : domainservice {     static bookauthordomainservice()     {         database.setinitializer<bookauthormodelcontext>(new bookauthormodelinitializer());     }      public bookauthordomainservice()     {         this.m_modelcontext = new bookauthormodelcontext();     }       public iqueryable<author> getauthors()     {         return this.m_modelcontext.authors.include("books");     }      public void insertauthor(author author)     {         this.m_modelcontext.insert(author);     }      public void updateauthor(author author)     {         this.m_modelcontext.update(author, this.changeset.getoriginal(author));     }      public void deleteauthor(author author)     {         this.m_modelcontext.delete(author);     }       public iqueryable<book> getbooks()     {         return this.m_modelcontext.books;//.include("authors");     }      public void insertbook(book author)     {         this.m_modelcontext.insert(author);     }      public void updatebook(book author)     {         this.m_modelcontext.update(author, this.changeset.getoriginal(author));     }      public void deletebook(book author)     {         this.m_modelcontext.delete(author);     }       protected override void dispose(bool disposing)     {         if (disposing)             this.m_modelcontext.dispose();         base.dispose(disposing);     }      protected override bool persistchangeset()     {         this.m_modelcontext.savechanges();         return base.persistchangeset();     }       private bookauthormodelcontext m_modelcontext; } 

the sql-tables created expected. in client application i'm using radgridview domaindatasource:

<usercontrol>     <usercontrol.resources>         <webservices:bookauthordomaincontext x:name="bookauthordomaincontext"/>     </usercontrol.resources>      <riacontrols:domaindatasource x:name="authordomaindatasource"                                   domaincontext="{staticresource bookauthordomaincontext}" queryname="getauthorsquery"                                                                   d:designdata="{d:designinstance webmodels:author, createlist=true}">      <telerik:radgridview x:name="authorgridview" datacontext="{binding elementname=authordomaindatasource}"                           itemssource="{binding data}" isbusy="{binding isbusy}"/> </usercontrol> 

now things interesting. if add 2 records empty database - 1 author table , other book table - both records 'id' field '1'. interesting thing is, getauthorsquery() included books adds book authors 'books' property. there no entry in created author_book (join-)table. so, i've started sql-profiler see going here. that's found out:

select  [project1].[id] [id],  [project1].[name] [name],  [project1].[c1] [c1],  [project1].[id1] [id1],  [project1].[name1] [name1] ( select      [limit1].[id] [id],      [limit1].[name] [name],      [join1].[id] [id1],      [join1].[name] [name1],      case when ([join1].[author_id] null) cast(null int) else 1 end [c1]       (select top (20) [c].[id] [id], [c].[name] [name]         [dbo].[author] [c] ) [limit1]     left outer join  (select [extent2].[author_id] [author_id], [extent3].[id] [id], [extent3].[name] [name]          [dbo].[author_book] [extent2]         inner join [dbo].[book] [extent3] on [extent3].[id] = [extent2].[book_id] ) [join1] on [limit1].[id] = [join1].[author_id] )  [project1] order [project1].[id] asc, [project1].[c1] asc 

why doing this? use many-to-many relation, happy use uni-directional relation (at least working).

thanks in advance help.

i'm not using attributes, maps. never less, hope you'll find useful.

this how write many-to-many relationship between authors , books, , able access book it's author , viceversa, well.

the following complete example, can copy&paste&compile.

using system; using system.collections.generic; using system.linq; using system.text; using wordandimages.entities; using system.data.entity; using system.data.entity.modelconfiguration; using system; using system.collections.generic; using system.linq; using system.text; using system.data.entity; using system.data.entity.modelconfiguration; using system.componentmodel.dataannotations;  namespace bookstore {     public class author     {         public int id { get; set; }         public string name { get; set; }         public virtual icollection<book> books { get; set; }         public author()         {             books = new list<book>();         }     }      public class book     {         public int id { get; set; }         public string title { get; set; }         public virtual icollection<author> authors { get; set; }         public book()         {             authors = new list<author>();         }     }      public class context : dbcontext     {         static context()         {             database.setinitializer<context>(null);         }          public dbset<author> authors { get; set; }         public dbset<book> books { get; set; }          protected override void onmodelcreating(dbmodelbuilder modelbuilder)         {             modelbuilder.configurations.add(new authormap());             modelbuilder.configurations.add(new bookmap());         }     }      public class bookmap : entitytypeconfiguration<book>     {         public bookmap()         {             this.hasmany(t => t.authors)             .withmany(a => a.books)             .map(t => t.totable("authorsbooks").mapleftkey("book_id").maprightkey("author_id"));         }     }      public class authormap : entitytypeconfiguration<author>     {         public authormap()         {             this.hasmany(a => a.books)             .withmany(b => b.authors)             .map(t => t.totable("authorsbooks").mapleftkey("author_id").maprightkey("book_id"));         }     }      class program     {         static void main(string[] args)         {              #region saving              var context = new context();             context.database.delete();             context.database.createifnotexists();              var book1 = new book { title = "joy" };             var book2 = new book { title = "happy" };              var author1 = new author { name = "lisa" };             var author2 = new author { name = "john" };             var author3 = new author { name = "luca" };              book1.authors.add(author1);             book1.authors.add(author2);              book2.authors.add(author1);             book2.authors.add(author3);              context.books.add(book1);             context.books.add(book2);             context.savechanges();              #endregion              #region accessing book it's author , viceversa              var context2 = new context();               var recovered_book1 = context2.books.where(b => b.title == "joy").firstordefault();             console.writeline(string.format("book1 has title {0} , has {1} authors", recovered_book1.title, recovered_book1.authors.count));             foreach (var author in recovered_book1.authors)                 console.writeline(author.name);              var recovered_book2 = context2.books.where(b => b.title == "joy").firstordefault();             console.writeline(string.format("book2 has title {0} , has {1} authors", recovered_book2.title, recovered_book2.authors.count));             foreach (var author in recovered_book1.authors)                 console.writeline(author.name);               var recovered_author1 = context2.authors.where(a => a.name == "lisa").firstordefault();             console.writeline(string.format("{0} wrote {1} books", recovered_author1.name, recovered_author1.books.count));             foreach (var book in recovered_author1.books)                 console.writeline(book.title);              console.readline();             #endregion         }     } } 

when comes recover book database, runs query

select top (1)  [extent1].[id] [id],  [extent1].[title] [title] [dbo].[books] [extent1] n'joy' = [extent1].[title] 

when recovers (with lazy load) authors, runs

exec sp_executesql n'select  [extent2].[id] [id],  [extent2].[name] [name]  [dbo].[authorsbooks] [extent1] inner join [dbo].[authors] [extent2] on [extent1].[author_id] = [extent2].[id] [extent1].[book_id] = @entitykeyvalue1',n'@entitykeyvalue1 int',@entitykeyvalue1=1