Linq to SQL using Repository Pattern: Object has no supported translation to SQL

Question

I have been scratching my head all morning behind this but still haven't been able to figure out what might be causing this.

I have a composite repository object that references two other repositories. I'm trying to instantiate a Model type in my LINQ query (see first code snippet).

public class SqlCommunityRepository : ICommunityRepository {



    private WebDataContext _ctx;
    private IMarketRepository _marketRepository;
    private IStateRepository _stateRepository;

    public SqlCommunityRepository(WebDataContext ctx, IStateRepository stateRepository, IMarketRepository marketRepository)
    {
        _ctx = ctx;
        _stateRepository = stateRepository;
        _marketRepository = marketRepository;
    }

    public IQueryable<Model.Community> Communities
    {
        get
        {
            return (from comm in _ctx.Communities
                    select new Model.Community
                    {
                        CommunityId = comm.CommunityId,
                        CommunityName = comm.CommunityName,
                        City = comm.City,
                        PostalCode = comm.PostalCode,
                        Market = _marketRepository.GetMarket(comm.MarketId),
                        State = _stateRepository.GetState(comm.State)
                    }
                    );
        }
    }
}

The repository objects that I'm passing in look like this

public class SqlStateRepository : IStateRepository {



private WebDataContext _ctx;

public SqlStateRepository(WebDataContext ctx)
        {
            _ctx = ctx;
        }

    public IQueryable<Model.State> States
    {
        get
        {
            return from state in _ctx.States
                   select new Model.State()
                   {
                       StateId = state.StateId,
                       StateName = state.StateName
                   };
        }
    }

    public Model.State GetState(string stateName)
    {
        var s = (from state in States
                 where state.StateName.ToLower() == stateName
                 select state).FirstOrDefault();

        return new Model.State()
        {
            StateId = s.StateId,
            StateName = s.StateName
        };
    }

AND

public class SqlMarketRepository : IMarketRepository {



private WebDataContext _ctx;

    public SqlMarketRepository(WebDataContext ctx)
    {
        _ctx = ctx;
    }

    public IQueryable<Model.Market> Markets
    {
        get
        {
            return from market in _ctx.Markets
                   select new Model.Market()
                              {
                                  MarketId = market.MarketId,
                                  MarketName = market.MarketName,
                                  StateId = market.StateId
                              };
        }
    }

    public Model.Market GetMarket(int marketId)
    {
        return (from market in Markets
                where market.MarketId == marketId
                select market).FirstOrDefault();
    }
}

This is how I'm wiring it all up:

        WebDataContext ctx = new WebDataContext();
        IMarketRepository mr = new SqlMarketRepository(ctx);
        IStateRepository sr = new SqlStateRepository(ctx);
        ICommunityRepository cr = new SqlCommunityRepository(ctx, sr, mr);
        int commCount = cr.Communities.Count();

The last line in the above snippet is where it fails. When I debug through the instantiation (new Model.Community), it never goes into any of the other repository methods. I do not have a relationship between the underlying tables behind these three objects. Would this be the reason that LINQ to SQL is not able to build the expression tree right?

Amy B · Accepted Answer · 2010-08-03 15:13:39Z

1

These are non-hydrated queries, not fully-hydrated collections.

The Communities query differs from the other two because it calls methods as objects are hydrated. These method calls are not translatable to SQL.

Normally this isn't a problem. For example: if you say Communities.ToList(), it will work and the methods will be called from the objects as they are hydrated.

If you modify the query such that the objects aren't hydrated, for example: when you say Communities.Count(), linq to sql attempts to send the method calls into the database and throws since it cannot. It does this even though those method calls ultimately would not affect the resulting count.

The simplest fix (if you truly expect fully hydrated collections) is to add ToList to the community query, hydrating it.

answered Aug 3, 2010 at 15:13

Amy B

110k21 gold badges140 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Praveen Over a year ago

That did it. It doesn't fire the query until you call a method on IQueryable?

Amy B Over a year ago

Most Linq methods are deferred - they do not evaluate the query until the result is enumerated. foreach and ToList are both means to enumerate a query.

Robert Harvey · Accepted Answer · 2010-08-03 00:02:02Z

0

Try adding another repository method that looks like this:

public int CommunitiesCount()
{
    get { return _ctx.Communities.Count(); }
}

This will allow you to return a count without exposing the entire object tree to the user, which is what I think you're trying to do anyway.

As you may have already guessed, I suspect that what you are calling the anonymous types are at fault (they're not really anonymous types; they are actual objects, which you are apparently partially populating in an effort to hide some of the fields from the end user).

answered Aug 3, 2010 at 0:02

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

2 Comments

Praveen Over a year ago

I'm not sure if the Count() method itself is the culprit. When I do a cr.Communities, I'm expecting a fully hydrated IQueryable collection. mr.Markets.Count() and sr.States.Count() return the expected results.

Praveen Over a year ago

Yes, _ctx.Communities.Count() gives me the right count. However, I'm looking for communityRepository.Communities, an IQueryable<Model.Community> list.

Collectives™ on Stack Overflow

Linq to SQL using Repository Pattern: Object has no supported translation to SQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related