Как я могу сделать вложенную проекцию в запросе Linq при использовании предложения group by?
Я пытаюсь работать с сгруппированными данными, возвращающимися из SQL. Метод, который я пишу, заключается в предоставлении данных для экрана "Обзор состояния дел". Он должен производить вложенный XML-документ.
Теперь я мог бы сделать это простым способом, но я пытаюсь узнать, возможно ли использовать оператор linq "group by", а затем проецировать уже вложенные данные. (самый простой способ - просто извлечь данные в табличном виде из базы данных, а затем выполнить цикл for, формируя документ XML для вывода)
Вот иерархия данных:
Каждый случай имеет тип Debt, а каждый тип Debt имеет клиента.
Вот SQL, который извлекает данные:
SELECT ClientNames.ClientID ,
ClientNames.ClientCode ,
ClientNames.ClientName ,
DebtTypes.DebtTypeID ,
DebtTypes.DebtTypeShortDesc ,
DebtTypes.DebtTypeLongDesc ,
Cases.CurrentStateCode ,
SUM(1 - CAST(Cases.CaseClosed AS INT)) AS OpenCaseCount ,
SUM(CAST(Cases.CaseClosed AS INT)) AS ClosedCaseCount ,
SUM(CAST(Cases.CaseOnHold AS INT)) AS OnHoldCaseCount ,
SUM(CAST(Cases.CaseReferred AS INT)) AS ReferredCaseCount ,
COUNT(Cases.CaseID) AS TotalCaseCount ,
SUM(Cases.CaseTotalPaid) AS TotalAmountPaid ,
SUM(Cases.CaseCurrentOutstandingAmount) AS TotalAmountOutstanding,
SUM(Cases.CaseTotalDebtWrittenOff) AS TotalAmountWrittenOff ,
SUM(Cases.CaseTotalDebtCancelled) AS TotalAmountCancelled
FROM ClientNames
INNER JOIN ClientDebtTypes
ON ClientNames.ClientID = ClientDebtTypes.ClientID
INNER JOIN DebtTypes
ON ClientDebtTypes.DebtTypeID = DebtTypes.DebtTypeID
INNER JOIN Cases
ON ClientDebtTypes.ClientDebtTypeID = Cases.CaseClientDebtTypeID
GROUP BY ClientNames.ClientID ,
ClientNames.ClientCode ,
ClientNames.ClientName ,
DebtTypes.DebtTypeID ,
DebtTypes.DebtTypeShortDesc,
DebtTypes.DebtTypeLongDesc ,
Cases.CurrentStateCode
ORDER BY ClientNames.ClientID,
DebtTypes.DebtTypeID,
CurrentStateCode
Используя Linqer, он преобразует его в:
from clientnames in db.ClientNames
join clientdebttypes in db.ClientDebtTypes on clientnames.ClientID equals clientdebttypes.ClientID
join debttypes in db.DebtTypes on clientdebttypes.DebtTypeID equals debttypes.DebtTypeID
join cases in db.Cases on new { ClientDebtTypeID = clientdebttypes.ClientDebtTypeID } equals new { ClientDebtTypeID = cases.CaseClientDebtTypeID }
group new {clientnames, debttypes, cases} by new {
clientnames.ClientID,
clientnames.ClientCode,
clientnames.ClientName1,
debttypes.DebtTypeID,
debttypes.DebtTypeShortDesc,
debttypes.DebtTypeLongDesc,
cases.CurrentStateCode
} into g
orderby
g.Key.ClientID,
g.Key.DebtTypeID,
g.Key.CurrentStateCode
select new {
ClientID = (System.Int32?)g.Key.ClientID,
g.Key.ClientCode,
g.Key.ClientName1,
DebtTypeID = (System.Int32?)g.Key.DebtTypeID,
g.Key.DebtTypeShortDesc,
g.Key.DebtTypeLongDesc,
g.Key.CurrentStateCode,
OpenCaseCount = (System.Int64?)g.Sum(p => 1 - Convert.ToInt32(p.cases.CaseClosed)),
ClosedCaseCount = (Int32?)g.Sum(p => Convert.ToInt32(p.cases.CaseClosed)),
OnHoldCaseCount = (Int32?)g.Sum(p => Convert.ToInt32(p.cases.CaseOnHold)),
ReferredCaseCount = (Int32?)g.Sum(p => Convert.ToInt32(p.cases.CaseReferred)),
TotalCaseCount = (Int64?)g.Count(p => p.cases.CaseID != null),
TotalAmountPaid = (System.Decimal?)g.Sum(p => p.cases.CaseTotalPaid),
TotalAmountOutstanding = (System.Decimal?)g.Sum(p => p.cases.CaseCurrentOutstandingAmount),
TotalAmountWrittenOff = (System.Decimal?)g.Sum(p => p.cases.CaseTotalDebtWrittenOff),
TotalAmountCancelled = (System.Decimal?)g.Sum(p => p.cases.CaseTotalDebtCancelled)
}
Теперь, как я уже упоминал, я мог бы остановиться там и перейти к циклу for для создания данных XML. Но я пытаюсь создать вложенную группу (IGrouping<ClientName,IGrouping<DebtType,SummaryClass>>), а затем проецировать данные во вложенном формате.
Теперь мы используем LinqToXsd для создания оболочек строгого типа для документов Xml, но по сути все это означает, что тип вывода:
private class ClientSummary
{
public string ClientName { get; set; }
public IList<DebtTypeSummary> DebtTypes { get; set; }
}
private class DebtTypeSummary
{
public string DebtType { get; set; }
public IList<StateCodeSummary> StateCodes { get; set; }
}
private class StateCodeSummary
{
public string StateCode { get; set; }
public int TotalCount { get; set; }
public decimal TotalAmountPaid { get; set; }
//etc
//etc
//etc
}
Теперь я дошел до написания следующего Linq:
var grouping = from cases in db.Cases
join clientdebttypes in db.ClientDebtTypes on cases.CaseClientDebtTypeID equals clientdebttypes.ClientID
join debttypes in db.DebtTypes on clientdebttypes.DebtTypeID equals debttypes.DebtTypeID
group cases by new ClientDebtTypePair() { ClientDebtType = clientdebttypes, DebtType = debttypes } into casesByClientDebtTypes
join clientnames in db.ClientNames on casesByClientDebtTypes.Key.ClientDebtType.ClientName equals clientnames
group casesByClientDebtTypes by clientnames;
var projected = from casesByClientDebtTypes in grouping
let client = casesByClientDebtTypes.Key
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
{
Client = new Client()
{
ClientID = client.ClientID,
DisplayName = client.ClientName1,
},
DebtTypes = from cases in casesByClientDebtTypes
let debttype = cases.Key.DebtType
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType.DebtTypesLocalType()
{
DebtType = new DebtType()
{
DebtTypeID = debttype.DebtTypeID,
Description = debttype.DebtTypeLongDesc,
DisplayName = debttype.DebtTypeShortDesc,
},
StatesCodes = from cases2 in cases
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType.DebtTypesLocalType.StatesCodesLocalType()
{
ClosedCasesCount = cases2.Sum(p => Convert.ToInt32(p.cases.CaseClosed))
который объединяет и группирует таблицы базы данных, а затем пытается спроецировать результат ClientSummary (имена классов отличаются, но это потому, что выше это упрощенное представление выходных классов). Я потерпел полный провал, когда углубился в таблицу Cases и обнаружил, что не совсем понимаю, как выполнять агрегирование функций. Похоже, они доступны только на IGrouping<K, T>и, кажется, я просто запутался.
Я также должен убедиться, что сводные данные рассчитаны на стороне сервера, поэтому отбрасывать миллионы дел было бы плохо.
Кто-нибудь может мне помочь с этим? Это вообще возможно?
С Уважением,
Джеймс.
------- ### ОБНОВЛЕНИЕ 1 ###-------
Хорошо, сегодня снова работаю над этим. Я решил использовать Linq2SQL для извлечения 2D-данных пакета, а затем переформатировать его с помощью Linq2Objects.
Вот с чего я начал:
var sql = from clientnames in db.ClientNames
join clientdebttypes in db.ClientDebtTypes on clientnames.ClientID equals clientdebttypes.ClientID
join debttypes in db.DebtTypes on clientdebttypes.DebtTypeID equals debttypes.DebtTypeID
join cases in db.Cases on new { ClientDebtTypeID = clientdebttypes.ClientDebtTypeID } equals new { ClientDebtTypeID = cases.CaseClientDebtTypeID }
group new { clientnames, debttypes, cases } by new
{
clientnames.ClientID,
clientnames.ClientCode,
clientnames.ClientName1,
debttypes.DebtTypeID,
debttypes.DebtTypeShortDesc,
debttypes.DebtTypeLongDesc,
cases.CurrentStateCode
} into g
orderby
g.Key.ClientID,
g.Key.DebtTypeID,
g.Key.CurrentStateCode
select new
{
Client = new Client{ ClientID = g.Key.ClientID, DisplayName = g.Key.ClientName1 },
DebtType = new DebtType{ DebtTypeID = g.Key.DebtTypeID, DisplayName = g.Key.DebtTypeShortDesc, Description = g.Key.DebtTypeLongDesc },
StateSummary = new LoadCaseStatusOverviewScreenOutput.ClientsLocalType.DebtTypesLocalType.StatesCodesLocalType()
{
StateCode = g.Key.CurrentStateCode,
OpenCasesCount = g.Sum(p => 1 - Convert.ToInt32(p.cases.CaseClosed)),
ClosedCasesCount = g.Sum(p => Convert.ToInt32(p.cases.CaseClosed)),
OnHoldCasesCount = g.Sum(p => Convert.ToInt32(p.cases.CaseOnHold)),
ReferredCasesCount = g.Sum(p => Convert.ToInt32(p.cases.CaseReferred)),
TotalCasesCount = g.Count(p => p.cases.CaseID != null),
TotalAmountPaid = g.Sum(p => p.cases.CaseTotalPaid),
TotalAmountOutstanding = g.Sum(p => p.cases.CaseCurrentOutstandingAmount),
TotalAmountWrittenOff = g.Sum(p => p.cases.CaseTotalDebtWrittenOff),
TotalAmountCancelled = g.Sum(p => p.cases.CaseTotalDebtCancelled),
}
};
var res = sql.ToList();
output.Clients = (from results in res
group results by results.Client into resultsByClient
from resultsByDebtType in
(from results in resultsByClient
group results by results.DebtType)
group resultsByDebtType by resultsByClient.Key into resultsByDebtTypeByClient
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
{
Client = resultsByDebtTypeByClient.Key,
DebtTypes = (from resultsByDebtType in resultsByDebtTypeByClient
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType.DebtTypesLocalType()
{
DebtType = resultsByDebtType.Key,
StatesCodes = (from results in resultsByDebtType
let summary = results.StateSummary
select results.StateSummary).ToList()
}).ToList()
}).ToList();
Это работает, но производит один набор Client/DebtType/Summary для каждого результата. Так что, хотя в этом случае есть только один клиент, я получаю 1300 клиентов, все они идентичны. Я упростил это до следующего:
output.Clients = (from results in res
group results by results.Client into resultsByClient
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
{
Client = resultsByClient.Key,
DebtTypes = null,
}).ToList();
Это производит 1300 клиентов. Затем я попробовал это:
output.Clients = (from results in res
group results by results.Client.ClientID into resultsByClient
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
{
Client = new Client { ClientID = resultsByClient.Key },
DebtTypes = null,
}).ToList();
И ЭТО производит ОДНОГО клиента (ура!). За исключением того, что я теряю всю информацию о клиенте (бу!), Догадываясь, что, сравнивая клиента по ссылке, а не по содержимому, я написал следующее:
public partial class Client
{
public static bool operator ==(Client left, Client right)
{
return left.ClientID == right.ClientID;
}
public static bool operator !=(Client left, Client right)
{
return left.ClientID != right.ClientID;
}
public override int GetHashCode()
{
return ClientID;
}
}
Это ничего не сделало. Это неоднократно звонит GetHashCode()который я выдумал, чтобы заставить его возвращать тот же хеш-код для любого соответствующего ClientID, но он все равно создал 1300 групп клиентов.
С Уважением,
Джеймс.
------- ### ОБНОВЛЕНИЕ 2 ###-------
Ладно, я подумал, что нужно сделать так, чтобы Linq2Sql выводил только простые значения для группировки по:
g.Key.ClientID,
g.Key.ClientName1,
g.Key.DebtTypeID,
g.Key.DebtTypeShortDesc,
g.Key.DebtTypeLongDesc,
А затем изменил тест Linq2Objects на:
output.Clients = (from results in res
group results by new { ClientID = results.ClientID, DisplayName = results.ClientName1 } into resultsByClient
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
{
Client = new Client { ClientID = resultsByClient.Key.ClientID, DisplayName = resultsByClient.Key.DisplayName },
DebtTypes = null,
}).ToList();
Это работает. Таким образом, анонимные типы сравниваются так, как я хочу, по содержанию, а не по ссылке (очевидно). Это не так:
output.Clients = (from results in res
group results by new SiDemClient { ClientID = results.ClientID, DisplayName = results.ClientName1 } into resultsByClient
select new LoadCaseStatusOverviewScreenOutput.ClientsLocalType()
{
Client = resultsByClient.Key,//new Client { ClientID = resultsByClient.Key.ClientID, DisplayName = resultsByClient.Key.DisplayName },
DebtTypes = null,
}).ToList();
Это все еще создает 1300 групп.
Итак, анонимные типы волшебным образом сравниваются, чего я не понимаю. Как я могу сделать мой Client класс сравнивать как анонимный тип?
С Уважением,
Джеймс.
------- ### НАЙТИ РЕШЕНИЕ ### -------
------- ### ОГРОМНОЕ ОГРОМНОЕ СПАСИБО ### -------
Мне нужно было переопределить Equals() метод вместо реализации оператора ==. Теперь группировка работает, и у меня есть замечательный XML-документ, который я хочу просмотреть!
public partial class SiDemClient
{
public override bool Equals(object obj)
{
if (obj is SiDemClient)
{
return this.ClientID.Equals(((SiDemClient)obj).ClientID);
}
return false;
}
public override int GetHashCode()
{
return ClientID;
}
}
Большое спасибо,
Джеймс.
1 ответ
Когда вы переопределяете GetHashCode Вы также должны переопределить Equals, == & != операторы не имеют значения.
Попробуйте с этим:
public partial class Client
{
public override bool Equals(object obj)
{
if (obj is Client)
{
return this.ClientID.Equals(((Client)obj).ClientID);
}
return false;
}
public override int GetHashCode()
{
return this.ClientID.GetHashCode();
}
}
Посмотрите, поможет ли это.