Is there a better way to fix a LINQ expression that could not be translated than my own solution?-CodePudding

For my LINQ query I have left joined a documents information table onto itself to grab only distinct documents with a top page count and certain social security numbers. I got an error on my first attempt. But I was able to fix it by toList() all the steps. However, I only want to ToList() the last part but I don't know how. I don't want each step to be doing a SQL call... I want to be building an IQUERYABLE in parts. Then once built do the SQL call once....

Here was the first attempt:

        // get documents of a type doc or eDoc that contains the social security number thats in a list of property ids
        var documents = db.DocListFullPathWithTagsLimitedVws.AsNoTracking()
           .Where(s => listOfSSNs.Contains(s.PropId)
            && s.StrVal == SSNparam && (s.DocumentType == "Doc" ||
           s.DocumentType == "eDoc"));

        // get top page count of each document
        var queryCount = documents.GroupBy(x => new { x.Tocid, x.PropId })
                     .Select(x => new
                     {
                         Tocid = x.Key.Tocid,
                         PropId = x.Key.PropId,
                         PageNum = x.Count()
                     });

        // distinct as the documents can be repeated from property ids
        var queryDistinct = queryCount.DistinctBy(x => x.Tocid);


        // left join on orginal documents to get 
        var queryCombine =
                    (from document in documents
                     join qd in queryDistinct on document.Tocid equals qd.Tocid into gj
                     from subset in gj.DefaultIfEmpty()
                     select new DocListFullPathWithTagsLimitedVw
                     {
                         DocumentName = document.DocumentName,
                         Tocid = document.Tocid,
                         EdocStoreid = document.EdocStoreid,
                         Storeid = document.Storeid,
                         EdocExt = document.EdocExt,
                         PropId = document.PropId,
                         StrVal = document.StrVal,
                         FullPathAndFilename = document.FullPathAndFilename,
                         DocumentType = document.DocumentType,
                         VolName = document.VolName,
                         Modified = document.Modified,
                         Created = document.Created,
                         PageNum = subset.PageNum
                     }).ToList();

        return queryCombine;

Gets this error Message:

                         [LaserficheDocFinder] : System.InvalidOperationException: The LINQ expression 'DbSet<DocListFullPathWithTagsLimitedVw>()
                .Where(s => __listOfSSNs_0
                    .Contains(s.PropId) && s.StrVal == __SSNparam_1 && s.DocumentType == "Doc" || s.DocumentType == "eDoc")
                .GroupBy(x => new { 
                    Tocid = x.Tocid, 
                    PropId = x.PropId
                 })
                .Select(x => new { 
                    Tocid = x.Key.Tocid, 
                    PropId = x.Key.PropId, 
                    PageNum = x
                        .AsQueryable()
                        .Count()
                 })
                .DistinctBy(x => x.Tocid)' could not be translated. Either rewrite the query in a form that can be translated, 
                        or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 
                        'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Here is my solution. (but I want it all to be IQueryable untill the end...)

        var documents = db.DocListFullPathWithTagsLimitedVws.AsNoTracking()
                       .Where(s => listOfSSNs.Contains(s.PropId)
                        && s.StrVal == SSNparam && (s.DocumentType == "Doc" ||
                       s.DocumentType == "eDoc" )).ToList();

        // get top page count of each document
        var queryCount = documents.GroupBy(x => new { x.Tocid, x.PropId })
                     .Select(x => new
                     {
                         Tocid = x.Key.Tocid,
                         PropId = x.Key.PropId,
                         PageNum = x.Count()
                     }).ToList();

        // distinct as the documents can be repeated from props
        var queryDistinct = queryCount.DistinctBy(x => x.Tocid);

        var queryDistinctList = queryDistinct.AsEnumerable().ToList();


        var queryCombine =
                    (from document in documents
                     join qd in queryDistinctList on document.Tocid equals qd.Tocid into gj
                     from subset in gj.DefaultIfEmpty()
                     where (document.DocumentType == "Doc" && document.PageNum == subset.PageNum && document.PropId == subset.PropId)
                     || (document.DocumentType == "eDoc" && document.PageNum == null && subset.PageNum == 1 && document.PropId == subset.PropId )
                     || (document.DocumentType == "eDoc" && document.PageNum != null && document.PageNum == subset.PageNum && document.PropId == subset.PropId)
                     || (document.FullPathAndFilename == null && document.PageNum == null && subset.PageNum == 1 && document.PropId == subset.PropId)
                     select new DocListFullPathWithTagsLimitedVw
                     {
                         DocumentName = document.DocumentName,
                         Tocid = document.Tocid,
                         EdocStoreid = document.EdocStoreid,
                         Storeid = document.Storeid,
                         EdocExt = document.EdocExt,
                         PropId = document.PropId,
                         StrVal = document.StrVal,
                         FullPathAndFilename = document.FullPathAndFilename,
                         DocumentType = document.DocumentType,
                         VolName = document.VolName,
                         Modified = document.Modified,
                         Created = document.Created,
                         PageNum = subset.PageNum
                     }).ToList();

        return queryCombine;

CodePudding user response：

You can remove DistinctBy and use other technique how to get first element. It is possible to add OrderBy if it is omitted. Also Left join is not needed, outer items should exist.

// get documents of a type doc or eDoc that contains the social security number thats in a list of property ids
var documents = db.DocListFullPathWithTagsLimitedVws.AsNoTracking()
    .Where(s => listOfSSNs.Contains(s.PropId)
        && s.StrVal == SSNparam && (s.DocumentType == "Doc" ||
        s.DocumentType == "eDoc"));

// get top page count of each document
var queryCount = documents.GroupBy(x => new { x.Tocid, x.PropId })
    .Select(x => new
    {
        Tocid = x.Key.Tocid,
        PropId = x.Key.PropId,
        PageNum = x.Count()
    });

var queryCombine =
    (from document in documents
    from subset in queryCount.Where(x => x.TocId == document.Tocid).Take(1) 
    select new DocListFullPathWithTagsLimitedVw
    {
        DocumentName = document.DocumentName,
        Tocid = document.Tocid,
        EdocStoreid = document.EdocStoreid,
        Storeid = document.Storeid,
        EdocExt = document.EdocExt,
        PropId = document.PropId,
        StrVal = document.StrVal,
        FullPathAndFilename = document.FullPathAndFilename,
        DocumentType = document.DocumentType,
        VolName = document.VolName,
        Modified = document.Modified,
        Created = document.Created,
        PageNum = subset.PageNum
    }).ToList();

return queryCombine;