问题描述
这真让我感到难过.我的数据库中有四个表，不幸的是，设计此表的人没有创建引用约束.因此，没有可用的导航属性.
This really has me stumped. I have four tables in the database, and unfortunately the person who designed this table didn't create referential constraints. So, there is no navigation properties available.
四个表是:
CiscoPhoneReport
ApplicationSummary
CSQActivityReport
CallDistributionSummary
想法是，对于CiscoPhoneReport中的每个PhoneReportID，都有一个ApplicationSummary，三个CSQActivityReport和三个CallDistributionSummary.
The idea is that for each PhoneReportID in CiscoPhoneReport, there is one ApplicationSummary, three CSQActivityReport, and three CallDistributionSummary.
我想要JSON格式的输出如下:
I want the output as below in JSON format:
`[{
"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:
For this, I created 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表中的数据.
The CSQModel class has needed data from both CSQActivityReport and CallDistributionSummary tables.
我能够使用表连接创建linq语句，如下所示.
I was able to create a linq statement with table joins as below.
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中的内部联接一样.但这不是我想要的.
The result I'm getting is a set of data with 9 rows, which makes sense - as in inner join in SQL. But this is not what I wanted.
如何获取上述JSON格式的数据?我完全不知道.
How can I obtain the data as in the JSON format above? I couldn't figure it out at all.
推荐答案
我认为您看到9条记录的部分原因是因为您使用的语法是 Linq中的左外部联接.
I think part of the reason you are seeing 9 records is because the syntax you are using is the one for left outer joins in Linq.
可能有用的方法是使用子查询以所需的格式获取所需的数据.
What might work is using subqueries to get the data you want in a format you want it.
例如
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还是类型为CSQModel的基本ICollection.您可以根据需要为CallDistributionSummary编写另一个子查询.
You were right that you need the CSQModels to be some sort of collection, be it a List or even a basic ICollection of type CSQModel. You can write another sub query for the CallDistributionSummary as needed.
这篇关于使用Lambda/Linq C#和DTO进行多表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！