在linq查询中的详细表总和 [英] Detail table sum in linq query

问题描述

嗨朋友们，

我有三张桌子以下是



VisitDetails

Hi friends,
I have three table these are follows

VisitDetails

VisitId    visitCode   visitDate 
1          V001      15-12-2015

PatientBill

PatientBill

PatientBillId    VisitID     BillCode     PatientPayable      CompanyPayable
1                1           B001          100                 50
2                1           B002          80                  40

PatientBillDetails

PatientBillDetails

PBillDetailsId      PatientBIllId      Gross    Discount    NetPayable
1                   1                  100      25          75
2                   1                  100      25          75
3                   2                  80       20          60
4                   2                  80       20          60

我想要这样的记录

第一输出

I want record like that
First Output

VisitCode      PatientPayable        CompanyPayable      NetPayable
V001           180                   90                  270

第二次输出

Second Output

VisitCode     BillCode     PatientPayable        CompanyPayable      NetPayable
V001          B001         100                   50                  150
V001          B002         80                    40                  120

注意： - 有一些标准，我不能为NetPayable添加PatientPayable和CompanyPayable金额。每次NetPayable金额应来自PatientBillDetails。



我尝试过：

Note:- There are some criteria so, I can't add PatientPayable and CompanyPayable amount for NetPayable. Everytime NetPayable amount should come from PatientBillDetails.

What I have tried:

var result= from VD in VisitDetails 
            join PB in PatientBill on VD.VisitId equal PB.VisitId 
            join PBD in PatientBillDetails on PB.PatientBillId equal PBD.PatientBillId
group new
{
PB.PatientPayable,
PB.CompanyPayable,
PBD.NetPayable
}
by new
{
VD.VisitCode
}
into data
select new
{
VisitCode= data.Key.VisitCode,
PatientPayable= data.sum(x=> x.PatientPayable),
CompanyPayable= data.sum(x=> x.CompanyPayable),
NeyPayable= data.sum(x=> x.NetPayable)
}

现在我的输出是

Now my output is

VisitCode     PatientPayable     ComapanyPayable      NetPayable
V001          360                180                  270

但我想要像这样的输出

But I want Output like this

VisitCode     PatientPayable     ComapanyPayable      NetPayable
V001          180                90                  270

推荐答案

对于这种要求，您必须以不同的方式分组数据。请查看以下示例：

For such of requirement you have to "group" data in a different way. Please, check out below example:

DataTable VisitDetails = new DataTable();
VisitDetails.Columns.Add(new DataColumn("VisitId", typeof(int)));
VisitDetails.Columns.Add(new DataColumn("visitCode", typeof(string)));
VisitDetails.Columns.Add(new DataColumn("visitDate", typeof(DateTime)));
VisitDetails.Rows.Add(new object[]{1, "V001", new DateTime(2015,12,15)});

DataTable PatientBill = new DataTable();
PatientBill.Columns.Add(new DataColumn("PatientBillId", typeof(int)));
PatientBill.Columns.Add(new DataColumn("VisitId", typeof(int)));
PatientBill.Columns.Add(new DataColumn("BillCode", typeof(string)));
PatientBill.Columns.Add(new DataColumn("PatientPayable", typeof(int)));
PatientBill.Columns.Add(new DataColumn("CompanyPayable", typeof(int)));
PatientBill.Rows.Add(new object[]{1, 1, "B001", 100, 50});
PatientBill.Rows.Add(new object[]{2, 1, "B002", 80, 40});

DataTable PatientBillDetails = new DataTable();
PatientBillDetails.Columns.Add(new DataColumn("PBillDetailsId", typeof(int)));
PatientBillDetails.Columns.Add(new DataColumn("PatientBIllId", typeof(int)));
PatientBillDetails.Columns.Add(new DataColumn("Gross", typeof(int)));
PatientBillDetails.Columns.Add(new DataColumn("Discount", typeof(int)));
PatientBillDetails.Columns.Add(new DataColumn("NetPayable", typeof(int)));
PatientBillDetails.Rows.Add(new object[]{1, 1, 100, 25, 75});
PatientBillDetails.Rows.Add(new object[]{2, 1, 100, 25, 75});
PatientBillDetails.Rows.Add(new object[]{3, 2, 80, 20, 60});
PatientBillDetails.Rows.Add(new object[]{4, 2, 80, 20, 60});


var result2 = (from PB in PatientBill.AsEnumerable()
			select new
			{
				VisitId = PB.Field<int>("VisitId"),
				VisitCode = (from VD in VisitDetails.AsEnumerable()
								where VD.Field<int>("VisitId")==PB.Field<int>("VisitId")
								select VD.Field<string>("visitCode")).First(),
				BillCode = PB.Field<string>("BillCode"),
				PatientPayable = PB.Field<int>("PatientPayable"),
				CompanyPayable = PB.Field<int>("CompanyPayable"),
				NetPayable = (from PBD in PatientBillDetails.AsEnumerable()
								where PBD.Field<int>("PatientBIllId")==PB.Field<int>("PatientBIllId") 
								select PBD.Field<int>("NetPayable")).Sum(),
			}).ToList();
//see result #2
var result1 = (from r in result2
				group r by r.VisitCode into g
				select new
				{
					VisitCode = g.Key,
					PatientPayable = g.Sum(x=>x.PatientPayable),
					CompanyPayable = g.Sum(x=>x.CompanyPayable),
					NetPayable = g.Sum(x=>x.NetPayable)
				}).ToList();
//see result #1

结果＃1

Result #1

VisitCode PatientPayable CompanyPayable NetPayable
V001      180            90             270 

Resutl＃2

Resutl #2

VisitId VisitCode BillCode PatientPayable CompanyPayable NetPayable
1       V001      B001     100            50             150 
1       V001      B002     80             40             120

这篇关于在linq查询中的详细表总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！