LINQ to Entities does not recognize the method, 2 repositories

Question

I keep getting the error below on my code, and can't understand why it is having problems translating it to a query, it is pretty simple.

I have 2 repositories, Album and AlbumImage, when I fetch an album do I want a cover, that is a subselect in AlbumImages. What am I doing wrong here?

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[Sogaard.us.Cosplay.Data.AlbumImage] Get()' method, and this method cannot be translated into a store expression.

Album repository

    public class AlbumRepository : IRepository<Album>
    {
        private CosplayEntities _entities;
        private IRepository<AlbumImage> _imageRepository;

        public AlbumRepository(CosplayEntities entities, IRepository<AlbumImage> imageRepository)
        {
            _entities = entities;
            _imageRepository = imageRepository;
        }

        public IQueryable<Album> Get()
        {
            return (from a in _entities.Albums
                    select new Album()
                        {
                            Id = a.Id,
                            UserId  = a.UserId,
                            Name  = a.Name,
                            Created  = a.Created,
                            LastEdit  = a.LastEdit,
                            Description  = a.Description,
                            Views  = a.Views,
                            Location  = a.Location,
                            Photoshoot  = a.Photoshoot,
                            Cover = (from ai in _imageRepository.Get() where ai.AlbumId == a.Id orderby ai.Cover descending, ai.Id ascending select ai).FirstOrDefault(),
                        });
        }
}

AlbumImage repository

public class AlbumImageRepository : IRepository<AlbumImage>
{
    private CosplayEntities _entities;

    public AlbumImageRepository(CosplayEntities entities)
{
    _entities = entities;
}

public IQueryable<AlbumImage> Get()
{
    return (from ai in _entities.AlbumImages
            select new AlbumImage()
                {
                    Id = ai.Id,
                    AlbumId = ai.AlbumId,
                    UserId = ai.UserId,
                    Type = ai.Type,
                    Width = ai.Width,
                    Height = ai.Height,
                    Description = ai.Description,
                    Views = ai.Views,
                    Uploadet = ai.Uploadet,
                    LastView = ai.LastView,
                    Thumblink = ai.Thumblink,
                    Imagelink = ai.Imagelink,
                    Cover = ai.Cover
                });
}

This is the code i am getting the error on

    _albumImageRepository = new AlbumImageRepository(_entities);
    _albumRepository = new AlbumRepository(_entities, _albumImageRepository);
    _albumImagesTagRepository = new AlbumImagesTagRepository(_entities);
....

    var album = _albumRepository.Get().Where(x => x.Id == image.AlbumId).FirstOrDefault();

Update: I have commented the Cover = ... out in my IQueryable Get() so it is 2 simple select as object. And i still get the error in something as simple as

    model.Albums = (from a in _albumRepository.Get()
                    orderby a.Id descending
                    select new AlbumDisplayModel()
                        {
                            Album = a,
                            ImageCount = _albumImageRepository.Get().Where(x => x.AlbumId == a.Id).Count(),
                            User = _userRepository.Get().Where(x => x.Id == a.UserId).FirstOrDefault()
                        })
                        .Skip(AlbumsPrPage * (page - 1))
                        .Take(AlbumsPrPage).ToList();

Update 2: If i rewrite the IQueryable Get() to the following, do it work flawlessly, there there should really be no diffrence in how it is handled?

public IQueryable<Album> Get()
{
    return (from a in _entities.Albums
            select new Album()
                {
                    Id = a.Id,
                    UserId  = a.UserId,
                    Name  = a.Name,
                    Created  = a.Created,
                    LastEdit  = a.LastEdit,
                    Description  = a.Description,
                    Views  = a.Views,
                    Location  = a.Location,
                    Photoshoot  = a.Photoshoot,
                    Cover = (from ai in _entities.AlbumImages where ai.AlbumId == a.Id orderby ai.Cover descending, ai.Id ascending select new AlbumImage()
                    {
                        Id = ai.Id,
                        AlbumId = ai.AlbumId,
                        UserId = ai.UserId,
                        Type = ai.Type,
                        Width = ai.Width,
                        Height = ai.Height,
                        Description = ai.Description,
                        Views = ai.Views,
                        Uploadet = ai.Uploadet,
                        LastView = ai.LastView,
                        Thumblink = ai.Thumblink,
                        Imagelink = ai.Imagelink,
                        Cover = ai.Cover
                    }).FirstOrDefault(),
                });
}

Update 3: Did a little test, and the problem seems to be with Entity framework, se the following code, The var linqAlbum = testClass.LinqAlbumGet().ToList(); executes without any problems and return the correct data, var eeAlbum = testClass.EEAlbumGet().ToList(); fails with the exception

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[RepositoryTest.TestAlbumCover] EEImageGet()' method, and this method cannot be translated into a store expression.

My test script

class Program
{
    static void Main(string[] args)
    {
        var linq = new LinqDataContext();
        var ee = new NewCosplayEntities();

        var testClass = new Test(linq, ee);

        var linqAlbum = testClass.LinqAlbumGet().ToList();
        var eeAlbum = testClass.EEAlbumGet().ToList();
    }
}


    public class Test
    {
        public NewCosplayEntities ee { get; set; }
        public LinqDataContext linq { get; set; }

        public Test(LinqDataContext linq, NewCosplayEntities ee)
        {
            this.linq = linq;
            this.ee = ee;
        }

        public IQueryable<TestAlbum> LinqAlbumGet()
        {
            return from a in linq.Albums
                   select new TestAlbum
                   {
                       Id = a.Id,
                       Name = a.Name,
                       Cover = (from i in LinqImageGet() where i.AlbumId == a.Id select i).FirstOrDefault()
                   };
        }

        public IQueryable<TestAlbumCover> LinqImageGet()
        {
            return from i in linq.AlbumImages
                   select new TestAlbumCover()
                   {
                       Id = i.Id,
                       AlbumId = i.AlbumId
                   };
        }

        public IQueryable<TestAlbum> EEAlbumGet()
        {
            return from a in ee.Albums
                   select new TestAlbum
                   {
                       Id = a.Id,
                       Name = a.Name,
                       Cover = (from i in EEImageGet() where i.AlbumId == a.Id select i).FirstOrDefault()
                   };
        }

        public IQueryable<TestAlbumCover> EEImageGet()
        {
            return from i in ee.AlbumImages
                   select new TestAlbumCover()
                   {
                       Id = i.Id,
                       AlbumId = i.AlbumId
                   };
        } 
    }

    public class TestAlbum
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public TestAlbumCover Cover { get; set; }
    }

    public class TestAlbumCover
    {
        public int Id { get; set; }

        public int AlbumId { get; set; }
    }

score 1 · Answer 1 · answered Mar 22 '13 at 15:58

1

Potentially its because you are wrapping the Album and AlbumImage in new references. I would remove that and do the projection after your query.

answered Mar 22 '13 at 15:58

Daniel A. White

187,200
47
362
445

I don't understand what you mean? – Androme Mar 22 '13 at 16:02
@DoomStone don't do the `.Select` part in your `Get`. – Daniel A. White Mar 22 '13 at 16:05
That will defeat the whole idear in using repositories – Androme Mar 22 '13 at 16:25
@DanielA.White Wouldn't `Get()` not work because [Linq queries performed against a database are translated to SQL before they can be executed](http://stackoverflow.com/questions/10156659/linq-to-entities-does-not-recognize-the-method-system-string-get-item-system-s)? – Bob. Mar 22 '13 at 17:49

score 1 · Answer 2 · answered Mar 22 '13 at 16:46

1

I don't think you can project into an entity and have each projection use a result from another IQueryable. If you replaced the contents of IQueryable<AlbumImage> Get() with this, it might work:

from a in _entities.Albums
join c in _imageRepository.Get() on a.Id equals c.AlbumId into acJoin
from ac in acJoin.DefaultIfEmpty()
select new Album()
{
    Id = a.Id,
    etc..,
    etc..,
    Cover = ac
}

I'm actually fairly certain that you will need to adjust this freehand query, but essentially it's joining the IQueryables, then projecting those results into your objects, instead of projecting to your objects then inserting an IQueryable into those results. Not the best explanation I know, but just look up "LINQ Left Join" or "Linq Left Outer Join" to see the syntax of what I'm describing here. Example

answered Mar 22 '13 at 16:46

Ocelot20

10,510
11
55
96

I just did a update to my question, are Linq not able to handle new selects in the select? I know Linq2sql handels this with ease? – Androme Mar 22 '13 at 16:49
EF purposely prohibits a lot of queries that allow ambiguous mixes between entity and object queries. If that *did* work in Linq-to-SQL, it likely would have caused an n+1 query where each sub-query would be executed individually. You can certainly select subsets of data, but you need to do it in a way that it will know how to get all the query information up front. – Ocelot20 Mar 22 '13 at 16:54
In your updates to the question, the new query still repeats the portion that EF has an issue with. I bet if you replaced `from a in _albumRepository.Get()` with `from a in _albumRepository.Get().ToList()` that it would work. Note: This isn't what you should do as a final solution, but hopefully will clear up where the issue is. – Ocelot20 Mar 22 '13 at 16:59
The difference is either that A) They are using different contexts. B) Referenced through a method that EF has no knowledge of. Either way, the preferred syntax would be to use the join. The sub-select you have there is quite unwieldy and the expressed join is kind of lost in the jumble. – Ocelot20 Mar 22 '13 at 17:47

score 1 · Accepted Answer · answered Mar 22 '13 at 19:03

Your problem comes in the ItemRepository for Albumn. Specifically because _entities has no knowledge of the _imageRepository type, so it doesn't know how to translate that type into the appropriate TSQL script. You could cast the _entities.Albums.ToList() to force the IQueryable into an IEnumerable before you try to access the _ImageRepository.Get() from the scope of the hydrated object instead of directly on the database instance. Realize that you are then going to see a perf hit on the n+1 database requests for the AlbumImage child objects for each Album.

    public IQueryable<Album> Get()
    {
        return (from a in _entities.Albums
                select new Album()
                    {
                        Id = a.Id,
                        UserId  = a.UserId,
                        Name  = a.Name,
                        Created  = a.Created,
                        LastEdit  = a.LastEdit,
                        Description  = a.Description,
                        Views  = a.Views,
                        Location  = a.Location,
                        Photoshoot  = a.Photoshoot,
                        Cover = (from ai in _imageRepository.Get() where ai.AlbumId == a.Id orderby ai.Cover descending, ai.Id ascending select ai).FirstOrDefault(),
                    });
    }

Ultimately, the problem is that your trying to use an ActiveRecord pattern rather than a true repository. Everything in a single IQueryable needs to be fetched through the same database context instance for parsing and tracking purposes.

I get that, but when i do it with Linq2Sql dose it work fine, and it automaticly detectes that relations and converte it into a left join. Is Entity Framework simply not advance enoth yet to do the features in Linq2Sql? — Androme, Mar 22 '13 at 20:19
I'm surprised to hear that L2S works with separate repositories and contexts. Are you sure you weren't accessing the a.AlbumImage rather than _imageRepository.Get? L2S and EF do have some different semantics, but I wouldn't say it isn't advanced enough. — Jim Wooley, Mar 24 '13 at 03:43
In the example, you are using the same context, but projecting into local (DTO) object types. It may be that L2S can lift the local type whereas EF requires that the local types be EF mapped models. You can test this by replacing the EEImageGet to return a AlbumImage rather than TestAlbumCover object. That being said, you are still going to run into issues in your original query with multiple contexts. — Jim Wooley, Apr 01 '13 at 13:35

LINQ to Entities does not recognize the method, 2 repositories

3 Answers3