Объединение нескольких таблиц с использованием lambda/linq C# с DTO
Это действительно поставило меня в тупик. У меня есть четыре таблицы в базе данных, и, к сожалению, человек, который разработал эту таблицу, не создавал ссылочные ограничения. Таким образом, нет доступных свойств навигации.
Четыре таблицы:
CiscoPhoneReport
ApplicationSummary
CSQActivityReport
CallDistributionSummary
Идея в том, что для каждого PhoneReportID в CiscoPhoneReport есть один ApplicationSummary, три CSQActivityReportи три CallDistributionSummary,
Я хочу вывод, как показано ниже в формате JSON:
`[{
"appSummary":{
"startDate":"2015-09-01T00:00:00",
"endDate":"2015-09-30T00:00:00",
"applicationName":"RationalDrugTherapy",
"callsPresented":14504,
"callsAbandoned":1992,
"callsHandled":12512
},
"csqModel":[
{
"startDate":null,
"csqid":"3",
"callsPresented":6271,
"avgQueueTime":"00:00:21",
"callsHandled":0,
"avgAnswerSpeed":"00:00:00",
"avgHandleTime":"00:02:08",
"callsHandledGreaterThan3t":5742,
"callsAbandoned":99,
"avgAbandonTime":"00:02:20",
"maxQueueTime":"00:25:26",
"maxHandleTime":"00:19:33",
"maxAbandonTime":"00:17:50"
},{
"startDate":null,
"csqid":"3",
"callsPresented":6271,
"avgQueueTime":"00:00:21",
"callsHandled":0,
"avgAnswerSpeed":"00:00:00",
"avgHandleTime":"00:02:08",
"callsHandledGreaterThan3t":1728,
"callsAbandoned":99,
"avgAbandonTime":"00:02:20",
"maxQueueTime":"00:25:26",
"maxHandleTime":"00:19:33",
"maxAbandonTime":"00:17:50"
}, {
"startDate":null,
"csqid":"3",
"callsPresented":6271,
"avgQueueTime":"00:00:21",
"callsHandled":0,
"avgAnswerSpeed":"00:00:00",
"avgHandleTime":"00:02:08",
"callsHandledGreaterThan3t":3363,
"callsAbandoned":99,
"avgAbandonTime":"00:02:20",
"maxQueueTime":"00:25:26",
"maxHandleTime":"00:19:33",
"maxAbandonTime":"00:17:50"
}]
}]`
Для этого я создал DTO:
`public class AppSummary
{
public string PhoneReportID { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public string ApplicationName { get; set; }
public int CallsPresented { get; set; }
public int CallsAbandoned { get; set; }
public int CallsHandled { get; set; }
}
`
`public class CSQModel
{
public string StartDate { get; set; }
public string CSQID { get; set; }
public int CallsPresented { get; set; }
public TimeSpan AvgQueueTime { get; set; }
public int CallsHandled { get; set; }
public TimeSpan AvgAnswerSpeed { get; set; }
public TimeSpan AvgHandleTime { get; set; }
public int CallsHandledGreaterThan3t { get; set; }
public int CallsAbandoned { get; set; }
public TimeSpan AvgAbandonTime { get; set; }
public TimeSpan MaxQueueTime { get; set; }
public TimeSpan MaxHandleTime { get; set; }
public TimeSpan MaxAbandonTime { get; set; }
}
`
`public class PhoneReport
{
public AppSummary AppSummary { get; set; }
//Initially, I had it like this
public CSQModel CSQModel { get; set; }
//I renamed the property as LIST to see if I could use it and add data to the list in linq, but I couldn't use the list within select expression in linq.
//public List<CSQModel> CSQModel { get; set; }
}
`
Класс CSQModel нуждался в данных от обоих CSQActivityReport а также CallDistributionSummary столы.
Мне удалось создать оператор linq с объединениями таблиц, как показано ниже.
var res = from cpr in db.CiscoPhoneReport
join app in db.ApplicationSummary on cpr.PhoneReportID equals app.PhoneReportID into g1
from appGroup in g1.DefaultIfEmpty()
join csq in db.CSQActivityReport on cpr.PhoneReportID equals csq.PhoneReportID into g2
from csqGroup in g2.DefaultIfEmpty()
join call in db.CallDistributionSummary on cpr.PhoneReportID equals call.PhoneReportID into g3
from callGroup in g3.DefaultIfEmpty()
where cpr.PhoneReportID == phoneReportID
select new PhoneReport
{
AppSummary = new AppSummary
{
StartDate = cpr.StartDate,
EndDate = cpr.EndDate,
ApplicationName = appGroup.ApplicationName,
CallsPresented = appGroup.CallsPresented,
CallsAbandoned = appGroup.CallsAbandoned,
CallsHandled = appGroup.CallsHandled
},
CSQModel = new CSQModel
{
CSQID = csqGroup.CSQID.ToString(),
CallsPresented = csqGroup.CallsPresented,
AvgQueueTime = csqGroup.AvgQueueTime,
AvgHandleTime = csqGroup.AvgHandleTime,
CallsHandledGreaterThan3t = callGroup.CallsHandledGreaterThan3t,
CallsAbandoned = csqGroup.CallsAbandoned,
AvgAbandonTime = csqGroup.AvgAbandonTime,
MaxQueueTime = csqGroup.MaxQueueTime,
MaxHandleTime = csqGroup.MaxHandleTime,
MaxAbandonTime = csqGroup.MaxAbandonTime
}
};
`
В результате я получаю набор данных с 9 строками, что имеет смысл - как при внутреннем объединении в SQL. Но это не то, что я хотел.
Как я могу получить данные как в формате JSON выше? Я не мог понять это вообще.
3 ответа
Я думаю, что одна из причин, по которой вы видите 9 записей, заключается в том, что вы используете синтаксис левого внешнего объединения в Linq.
Что может сработать, так это использование подзапросов для получения нужных вам данных в нужном вам формате.
Например
var res = from cpr in db.CiscoPhoneReport
join app in db.ApplicationSummary on cpr.PhoneReportID equals app.PhoneReportID
where cpr.PhoneReportID == phoneReportID
select new PhoneReport
{
AppSummary = new AppSummary
{
// Mappings
},
CSQModel = (from model in db.CSQActivityReport
where model.PhoneReportId == phoneReportID
select new CSQModel
{
// Mappings
}).ToList()
}
Вы были правы, что вам нужно CSQModels быть какой-то коллекцией, будь то List или даже основной ICollection типа CSQModel, Вы можете написать еще один подзапрос для CallDistributionSummary по мере необходимости.
Для каждой основной записи у вас есть 3 записи в 2 отдельных дочерних таблицах. Любое присоединение, которое вы сделаете, даст вам 9 записей (даже если вы перейдете прямо к T-SQL), если вы не добавите дополнительную информацию.
Один из способов сделать это в SQL - присоединить запись 1 из таблицы A к записи 1 из таблицы B, для этого вам понадобится индексатор. Одним из вариантов в T-SQL является использование ROW_NUMBER() Функция на каждой из дочерних таблиц и использовать это значение в соединении. Но ROW_NUMBER() не был расширен до LINQ.
Если вы не можете получить дубликаты записей и просто делать Distinct() вызов для каждого дочернего набора результатов. Тогда вы можете сделать это как два или 3 отдельных запроса.
ПРИМЕЧАНИЕ: Вы можете связать это в 3 набора результатов в сохраненном процессе. Вы можете легко заставить EntityFramework десериализовать каждый результат в ваши POCO.
var objCtx = ((IObjectContextAdapter)ctx).ObjectContext;
using (SqlCommand cmd = ctx.Database.Connection.CreateCommand() as SqlCommand)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "<your proc here>";
var param = cmd.CreateParameter();
param.ParameterName = "@param1";
param.Value = someValue;
cmd.Parameters.Add(param);
await cmd.Connection.OpenAsync();
using (var reader = await cmd.ExecuteReaderAsync())
{
var results = objCtx.Translate<type1Here>(reader).ToList();
reader.NextResult();
var results2 = objCtx.Translate<type2Here>(reader).ToList();
reader.NextResult();
var results3 = objCtx.Translate<type3Here>(reader).ToList();
reader.NextResult();
}
}
Вы можете сериализовать свой объект:
DataContractJsonSerializer serializer = new DataContractJsonSerializer(GenericObject.GetType());
MemoryStream ms = new MemoryStream();
serializer.WriteObject(ms, GenericObject);
string json = Encoding.UTF8.GetString(ms.ToArray());
ms.Close();
return json;