想要用 cAAA_TYPE, t1.cAAA_KIND, t1.cAAA_LEVEL 做去除重複的依據，
再用 cAAA_KIND, iAAA_SEQ 做排序
以下是目前的做法，能進一步簡化或是有其他簡易的做法嗎?
var dt = (from t1 in db.AAA
where t1.bAAA_CANCEL == false
&& t1.cAAA_LEVEL == "C"
//orderby t1.cAAA_KIND, t1.iAAA_SEQ //先排序再Distinct，會發生沒排序的錯誤
select new
{
t1.cAAA_TYPE,
t1.cAAA_KIND,
t1.cAAA_LEVEL,
//t1.iAAA_SEQ
}).Distinct()
.ToList().Select(t1 => new
{
cAAA_TYPE = t1.cAAA_TYPE,
cAAA_KIND = t1.cAAA_KIND,
cAAA_LEVEL = t1.cAAA_LEVEL,
iAAA_SEQ = (from t4 in db.AAA
where t4.bAAA_CANCEL == false
&& t4.cAAA_LEVEL == "C"
&& t4.cAAA_TYPE == t1.cAAA_TYPE
&& t4.cAAA_KIND == t1.cAAA_KIND
&& t4.cAAA_LEVEL == t1.cAAA_LEVEL
select t4.iAAA_SEQ).FirstOrDefault()
})
.OrderBy(t=>t.cAAA_KIND)
.ThenBy(t=>t.iAAA_SEQ)
.ToList();
已邀請的邦友 {{ invite_list.length }}/5
雖然不太明白為何要有這樣邏輯,但看起來你想要按照第一筆iAAA_SEQ來做排序?
沒猜錯你的SQL應該想要這樣的
select cAAA_TYPE,cAAA_KIND,cAAA_LEVEL
from AAA t1
group by cAAA_TYPE,cAAA_KIND,cAAA_LEVEL
order by cAAA_KIND,
(select top 1 t2.iAAA_SEQ from AAA t2 where t1.cAAA_TYPE = t2.cAAA_TYPE
and t1.cAAA_KIND = t2.cAAA_KIND and t1.cAAA_LEVEL = t2.cAAA_LEVEL)
LINQ可以這樣寫
var list = (
from t in AAAs
group t by new
{
t.CAAAtTYPE,
t.CAAAtKIND,
t.CAAAtLEVEL
} into t
orderby t.Key.CAAAtKIND, (from s in t
select s.IAAAtSEQ).First()
select t into s
select s.Key
).ToList();
補充
iThome鐵人賽
看影片追技術