EF: Fetching Object containing a list as property

Question

I have the following classes:

public class DbCatalogEntry
{
    public int id { get; set; }
    private string filename;
    public long size { get; set; }
    public int duration { get; set; }
    public int height { get; set; }
    public int width { get; set; }
    public string vcodec { get; set; }
    public List<CatalogAudioStreamEntry> audiostreams { get; set; }
    public string imdbId { get; set; }
    public int ownedByUser { get; set; }
    public string mxHash { get; set; }
}


public class CatalogAudioStreamEntry
{
    public int bitrate { get; set; }
    public string codec { get; set; }
    public string language { get; set; }
    public int channels { get; set; }
    public string features { get; set; }
}

This is a one to many relationship. I am trying to retrieve a list of DbCatalogEntry objects from the database and also filling the audiostreams in the same query.

I tried the following but it does not work:

var MovieEntryList = 
    (from vwCat in ctx.sp_GetMovieCatalog(getCurrentUserId())
      select new DbCatalogEntry()
          {
              id = vwCat.id,
              size = vwCat.size,
              duration = vwCat.duration,
              vcodec = vwCat.codec,
              Filename = vwCat.filename,
              imdbId = vwCat.imdb_id,
              mxHash = vwCat.mxhash,
              ownedByUser = (int)vwCat.owned,
              width = vwCat.width,
              height =  vwCat.height,
              audiostreams = 
                (from astr in ctx.audiostreamentry
                  where astr.movie_id == vwCat.id
                  select new CatalogAudioStreamEntry()
                      {
                          bitrate = astr.bitrate,
                          channels = astr.channels,
                          codec = astr.codec,
                          features = astr.features,
                          language = astr.language
                      }).ToList()
          }).ToList();

Search revealed that you cannot put a ToList() into a linq to entity query as it cannot be converted converted. I read multiple suggestions about changing audiostreams to IEnumerable but was not able to get this to work either. Most attempts compiled fine but faild during runtime with Unable to create a constant value of type...

Can anyone point me to the correct direction to solve this issue? The important thing is that the round trips to the database must be kept at a minimum so it would not be possible to create a client-side subquery for each DbCatalogEntry to fill the list.

Update #1:

To provide more details: This is the model that was generated by EF from my database:

sp_GetMovieCatalog(getCurrentUserId()) is a stored function on the SQL server which accepts one parameter which is used to filter the results.

What I want to do is selecting from movieenty and load all associated rows from audiostreamenty. This data should be used to create instances of DbCatalogEntry, so the result type would be List<DbCatalogEntry>.

Is that possible at all? Maybe there is a better/easier solution?

Answer 1

I believe, you have your problem, because trying to use same context for multiple queries

You can try it this way:

    var MovieEntryList = 
    (from vwCat in ctx.sp_MovieCatalog
    where vwCat.owned = currentUserId
    select new DbCatalogEntry()
      {
          id = vwCat.id,
          size = vwCat.size,
          duration = vwCat.duration,
          vcodec = vwCat.codec,
          Filename = vwCat.filename,
          imdbId = vwCat.imdb_id,
          mxHash = vwCat.mxhash,
          ownedByUser = (int)vwCat.owned,
          width = vwCat.width,
          height =  vwCat.height,
          audiostreams = vwCat.audiostreamentry.Select(astr=>
               new CatalogAudioStreamEntry()
                  {
                      bitrate = astr.bitrate,
                      channels = astr.channels,
                      codec = astr.codec,
                      features = astr.features,
                      language = astr.language
                  }).ToList()
      }).ToList();

Or this way:

    var MovieEntryList = ctx.audiostreamentry.where(p=>p.movieCatolog.ownedByUser - currentUserId)
    //.ToList() //if you call ToList() here futher will be LINQ to Object, and you want have most of problems
    .GroupBy(p=>new {Id = p.movieCatolog.id, Owner = p.movieCatolog.p.movieCatolog.id})
    .Select(p=> new DbCatalogEntry{
    id = p.Key.Id,
    ownedByUser = p.Key.Owner,
    audiostrams = p.Select(x=>new CatalogAudioStreamEntry
    {
                      bitrate = astr.bitrate,
                      channels = astr.channels,
                      codec = astr.codec,
                      features = astr.features,
                      language = astr.language
                  })

    })