Calling query inside query in entity framework core

Question

I want to do something like this

public string GetSiteTitleFromChangeHistory(int siteId)
{
    var changeHistories = changeHistoryRepository.GetAll(c => c.SiteRegistryId == siteId);
    var value = changeHistories.firstOrDefault(r=>r.State="Active")
    return value.Title;
}

public IQueryable<PendingReconfirmation> GetSitesForBusinessReconfirmationReminder(IList<StateStatus> stateStatusMappingIds,
    string country, int reminderDay)
{
    
    return from reg in repositorySpositeRegistry.GetAll(x => x.SiteUrlcountryCode != null
            && x.SiteUrlcountryCode.ToLower() == country.ToLower())
            
            select new PendingReconfirmation()
            {
                Id = reg.Id,
                SiteTitle = GetSiteTitleFromChangeHistory(reg.Id!).ToString() ?? reg.SiteTitle,
                
            };
}

Repository.GetAll look like this

public IQueryable<T> GetAll(Expression<Func<T, bool>>? filter = null)
{
    var query = entities.AsQueryable();
    if (filter != null)
        query = query.Where(filter);
    return query;
}

But I am getting error

The client projection contains a reference to a constant expression of '' through the instance method 'GetSiteTitleFromChangeHistory'. This could potentially cause a memory leak; consider making the method static so that it does not capture constant in the instance.

But I cant make the method as static . Any help will be highly appreciated

Then you have to repeat `LINQKit` functionality. Expression should be corrected before passing to EF translator. There is no other option. — Svyatoslav Danyliv, Feb 06 '23 at 12:28
you could use `Join` instead. I see it's doable in your query since site registry are sharing `SiteId`. — iSR5, Feb 13 '23 at 06:38

score 0 · Answer 1 · answered Feb 08 '23 at 18:26

0

Make this function GetSiteTitleFromChangeHistory as STATIC.

answered Feb 08 '23 at 18:26

score 0 · Answer 2 · answered Feb 10 '23 at 01:37

Is there a specific reason you split up GetSiteTitleFromChangeHistory into a separate method?

Otherwise you should be able to do a subquery like so, provided the queryable used for the subquery is based on a dbset in the same efcontext:

public IQueryable<PendingReconfirmation> GetSitesForBusinessReconfirmationReminder(IList<StateStatus> stateStatusMappingIds,
                                                                                   string             country, int reminderDay)
{
    return from reg in repositorySpositeRegistry.GetAll(x => x.SiteUrlcountryCode != null
                                                             && x.SiteUrlcountryCode.ToLower() == country.ToLower())
            
           select new PendingReconfirmation()
           {
               Id        = reg.Id,
               SiteTitle = changeHistoryRepository.GetAll(c => c.SiteRegistryId == reg.Id && c.State == "Active").FirstOrDefault().Title ?? reg.Title
           };
}

I have a reason to do that. I am calling GetSiteTitleFromChangeHistory in many places — Alias Varghese, Feb 13 '23 at 06:36

Calling query inside query in entity framework core

2 Answers2