locked
WCF Data Service with LINQ to Entities problem RRS feed

  • Question

  • OK, i have two tables artist(id_artist, name) and songs(id_songs,name) which are M:N relationship in third table artist_songs(id_artist,id_songs). I would like to query and return every artist and song for this artist. In SQL i would just match id-s and it would work. How i do this in LINQ to Entities with WCF Data Service.

     

     context = new TrackPODEntities(new Uri("Service.svc", UriKind.Relative));
    
     songsCollection = new DataServiceCollection<songs>();
    
    
    
    
    
    var query = from s in context.songs
    
          from a in context.artist
    
          from ars in context.artist_song
    
          where ars.id_artist == a.id_artist && s.id_song == ars.id_song
    
          select new { 
    
           Artist = a.name,
    
           Song = s.name
    
          
    
          };
    
    
    
    
    
    songsCollection.LoadAsync(query); --invalid argument
    
       
    
    
    
       songsCollection.LoadCompleted += new EventHandler<LoadCompletedEventArgs>(songsCollection_LoadCompleted);
    
    
    
      }
    
    
    
      void songsCollection_LoadCompleted(object sender, LoadCompletedEventArgs e)
    
      {
    
       auoFindSong.ItemsSource = songsCollection;
    
       
    
      }
    
    

     

    I know i should use navigation properties, but im new to WCF Data Services and would like to learn.

     I tried like this also:

    var query = from s in context.songs.Expand("artist_song") select s;
          
          var dsQuery = (DataServiceQuery<songs>)query;
          dsQuery.BeginExecute(res => auoFindSong.ItemsSource = dsQuery.EndExecute(res), null);
    • Moved by Jackie-SunModerator Monday, March 28, 2011 7:21 AM (From:ADO.NET Entity Framework and LINQ to Entities)
    Friday, March 25, 2011 10:32 AM

Answers

  • Hi,

    I tried this on the public sample OData service at http://services.odata.org/OData/OData.svc/

    Although there's no many-many relationship in that service model, in OData all relationships are unidirectional, so for example the relationship from Category -> Product (which is 1 - many), is exactly the same as in your sample from artists -> songs.

    A sample query to get all names of categories and all names of the products in those categories:

    DemoService ctx = new DemoService(new Uri("http://services.odata.org/OData/OData.svc/"));
    
    var query = from category in ctx.Categories
          select new Category
          {
            Name = category.Name,
            Products = new DataServiceCollection<Product>(
                  from product in category.Products
                  select new Product
                  {
                    Name = product.Name
                  })
          };
    
    foreach (var c in query)
    {
      Console.WriteLine("Category {0}", c.Name);
      foreach (var p in c.Products)
      {
        Console.WriteLine("Product {0}", p.Name);
      }
    }
    

    This works just fine.

    Thanks,

     


    Vitek Karas [MSFT]
    • Marked as answer by JonathanSmith Thursday, March 31, 2011 1:13 PM
    Thursday, March 31, 2011 9:23 AM
    Moderator

All replies

  • Hello JonathanSmith,

     

    Welcome to the EF Forum!

    According to your description, I think your issue has more relation with WCF Data Service, so I will move it to that forum for better and quicker support!

    By the way, I think you could read this article about Code First and WCF Data Service in EF 4.1 RC:

    http://blogs.msdn.com/b/adonet/archive/2011/03/21/using-wcf-data-services-with-entity-framework-4-1-and-code-first.aspx

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, March 28, 2011 7:21 AM
    Moderator
  • Hi,

    The query based on comparing primary and foreign keys will not work in WCF Data Services. You need to use navigation properties instead (as you already noted).

    Your query could be written like this:
    var query = from s in context.songs select new songs { name = s.Name, artist = new artist { name = s.artist.name } };

    That should translate to URL like this:
    /songs?$expand=artist&$select=name,artist/name

    In any case, if you say that soething fails, could you please include the exception (ideally with a full callstack) and if relevant also an HTTP trace of the request? (Fiddler is great for getting these).

    Thanks,


    Vitek Karas [MSFT]
    Monday, March 28, 2011 7:54 AM
    Moderator
  • Thanks for such quicky reply. I tried your way but i can't get the last part name = s.artist.name to work in itellisense.

     

    var query = from s in context.songs
                select new songs
                {
                  song_name = s.song_name,
                  artist_song = new artist { artist_name = s. }
                };
    

    In the last part artist_name = s. itellisense only shows me  artist_song, song_name, id_song.

     

     You can see my Model.edmx here if it helps.

    Tuesday, March 29, 2011 9:40 AM
  • Hello, Jackie Sun,

     

    Thanks for moving my post into the right forum. I've read this article and my WCF Data Service is working right, i can make simple queries and get data but when i make multiple join query i'm stuck, so im trying to understand how this works in EF.

    Regards,

    Jonathan.

     

    Tuesday, March 29, 2011 2:25 PM
  • I also tried this way:

     

    var query = from ars in context.artist_song
                select new songs
                {
    
                  song_name = ars.songs.song_name,
                  artist_song = new artist { artist_name = ars.artist.artist_name }
    
                };
    
    but i got Error 1 Cannot implicitly convert type 'Track.TrackSVC.artist' to 'System.Data.Services.Client.DataServiceCollection<'Track.TrackSVC.artist_song>'
    Tuesday, March 29, 2011 8:46 PM
  • Hi,

    The problem is that the songs and artists have a many to many relationship with an entity set in between. This is generally not the best model for OData. You would have to write the select like

    select new songs { artist_song = new artist_song { artist = new artist { artist_name = s.artis_song.artist.name } } }

    In short, you can't do ars.song.song_name without having the song in the middle projected out as an object as well.

    I would suggest you hide the join table (artist_song) from the end user. EF should be able to represent the relationship as many-many without the artist_song join table being exposed as entity set. Then you have only two entities to deal with.

    Thanks,


    Vitek Karas [MSFT]
    Tuesday, March 29, 2011 8:58 PM
    Moderator
  • How can i achieve this? By not selecting artis_song when mapping to database, and how would i query it?

     EDIT:

    So i found this article http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx

    and i made changes in my DB and made an update to my model , and still i don't know how to write a query. 

    Thanks

    Tuesday, March 29, 2011 9:18 PM
  • Hi,

    I tried this on the public sample OData service at http://services.odata.org/OData/OData.svc/

    Although there's no many-many relationship in that service model, in OData all relationships are unidirectional, so for example the relationship from Category -> Product (which is 1 - many), is exactly the same as in your sample from artists -> songs.

    A sample query to get all names of categories and all names of the products in those categories:

    DemoService ctx = new DemoService(new Uri("http://services.odata.org/OData/OData.svc/"));
    
    var query = from category in ctx.Categories
          select new Category
          {
            Name = category.Name,
            Products = new DataServiceCollection<Product>(
                  from product in category.Products
                  select new Product
                  {
                    Name = product.Name
                  })
          };
    
    foreach (var c in query)
    {
      Console.WriteLine("Category {0}", c.Name);
      foreach (var p in c.Products)
      {
        Console.WriteLine("Product {0}", p.Name);
      }
    }
    

    This works just fine.

    Thanks,

     


    Vitek Karas [MSFT]
    • Marked as answer by JonathanSmith Thursday, March 31, 2011 1:13 PM
    Thursday, March 31, 2011 9:23 AM
    Moderator
  • Thank you.
    Thursday, March 31, 2011 1:13 PM