對于我的 LINQ 查詢，我已經離開加入了一個檔案資訊表，以僅獲取具有首頁計數和某些社會安全號碼的不同檔案。我第一次嘗試時出錯。但我能夠通過 toList() 所有步驟修復它。但是，我只想 ToList() 最后一部分，但我不知道如何。我不希望每個步驟都進行 SQL 呼叫......我想部分構建 IQUERYABLE。然后一旦構建執行一次 SQL 呼叫......
這是第一次嘗試：
// 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;
收到此錯誤訊息：
[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.
這是我的解決方案。（但我希望這一切都是 IQueryable 直到最后......）
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;
uj5u.com熱心網友回復：
您可以洗掉DistinctBy并使用其他技術來獲取第一個元素。OrderBy如果省略，可以添加。也不需要左連接，外部專案應該存在。
// 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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/517674.html
標籤：C＃。网林克