使用多个联接方法语法将SQL SERVER SELECT转换为LINQ [英] Translate SQL SERVER SELECT to LINQ with multiple join Method Syntax
本文介绍了使用多个联接方法语法将SQL SERVER SELECT转换为LINQ的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何使用方法语法从以下SQL SELECT语句在linq中进行联接:
How could I do the join in linq from the following SQL SELECT statement by using method syntax :
SELECT distinct [LAB_RESULTS].ORDER_ID
,LAB_RESULTS.patient_no
,Patients.PATIENT_NAME
,labtests.TestId
,labtests.TestName
,[RESULT_NUMBER]
,TestsRanges.LowerLimit
,TestsRanges.UpperLimit
,TestsUnits.UnitName
FROM [dbo].[LAB_RESULTS]
inner join LabTests on LabTests.testid=LAB_RESULTS.TESTID
inner join TestsRanges on TestsRanges.TestId = LAB_RESULTS.TESTID
inner join patients on Patients.Patient_No = LAB_RESULTS.patient_no
inner join machines on lab_results.machine_id = machines.machine_id
inner join gender on gender.genderid = Patients.Gender
inner join TestsUnits on TestsUnits.UnitId = LabTests.UnitId
inner join Lab_orders_Cash on Lab_orders_Cash.cash_order_id = LAB_RESULTS.ORDER_ID
inner join PatientCat on (patientcat.CatId = Lab_orders_Cash.CatId)
inner join Lab_Hematology_Samples on Lab_Hematology_Samples.SAMPLE_ID = LAB_RESULTS.SAMPLE_ID
where lab_results.ORDER_ID = 2000000058
and (Lab_orders_Cash.catid = TestsRanges.CatId )
and TestsRanges.machine_id = LAB_RESULTS.machine_id
and (Patients.Gender = TestsRanges.PatientSex or TestsRanges.PatientSex = 1 )
我说了控制器的第一部分:
I stated the first part of the controller :
public ActionResult CashData(int id)
{
var tables = new Orders_Tables
{
LabResults = db.LAB_RESULTS.Where(o => o.ORDER_ID == id)
.Include(p => p.LabTests)
.Include(t => t.Patients).ToList(),
LabParaResult = db.LAB_PARA_RESULTS.Where(o => o.ORDER_ID == id).Include(t => t.Patients).ToList(),
LabCultureResults = db.LAB_CULTURE_RESULT.Where(o => o.ORDER_ID == id).ToList(),
LabMicroResults = db.LAB_MICRO_NEGATIVE_RESULT.Where(o => o.ORDER_ID == id).ToList(),
labCashView = db.LAB_RESULT_CASH_VIEW.Where(o => o.order_number == id).ToList(),
labtests = db.LabTests.ToList(),
patients = db.Patients.ToList()
};
}
现在对我来说很重要，可以加入TestsRanges，TestsUnits，PatientCat，机器和设备.控制器上具有此部分的性别表:
The important now for me to join TestsRanges , TestsUnits , PatientCat, machines & Gender tables with this part on the controller :
LabResults = db.LAB_RESULTS.Where(o => o.ORDER_ID == id)
.Include(p => p.LabTests)
.Include(t => t.Patients).ToList(),
我在数据库和外键中创建了完整的关系:
I created the complete relations in the database and foreign keys :
LabTests表包含Testid作为主键 其他表包括testid作为外键LAB_RESULTS，TestsRanges和TestsUnits
LabTests table contains Testid as primary key the others tables include testid as foreign key LAB_RESULTS,TestsRanges and TestsUnits
这是Orders_Tables类:
This is class Orders_Tables:
public class Orders_Tables
{
internal IQueryable<object> result { get; set; }
public Lab_Orders LabOrders { get; set; }
public Lab_orders_Cash LabOrdersCash { get; set; }
public Lab_Sample_status LabOrderStatus { get; set; }
public IEnumerable<LAB_RESULTS> LabResults { get; set; }
public IEnumerable<TestsRanges> testsRanges { get; set; }
public IEnumerable<LAB_CULTURE_RESULT> LabCultureResults { get; set; }
public IEnumerable<LAB_MICRO_NEGATIVE_RESULT> LabMicroResults { get; set; }
public IEnumerable<LabTests> labtests { get; set; }
public LAB_RESULTS_CLINIC_VIEW labViewResult { get; set; }
public IEnumerable<LAB_RESULT_CASH_VIEW> labCashView { get; set; }
public IEnumerable<LAB_PARA_RESULTS> LabParaResult { get; set; }
public Lab_Hematology_Samples LabSamples { get; set; }
public IEnumerable<Patients> patients { get; set; }
public IEnumerable<Customers> customers { get; set; }
}
我将控制器中的查询更改如下:
EDIT : I changed the query in controller as the following :
public ActionResult CashData(int id)
{
var tables = new Orders_Tables
{
testsRanges = db.TestsRanges.ToList(),
//LabResults = db.LAB_RESULTS.Where(o => o.ORDER_ID == id )
// .Include(p => p.LabTests)
// .Include(t => t.Patients).ToList(),
//LabResults = result,
result = (from labResult in db.LAB_RESULTS
join labTest in db.LabTests on labResult.TESTID equals labTest.TestId
join testRange in db.TestsRanges on labResult.TESTID equals testRange.TestId
where labResult.ORDER_ID == id
select new { labResult.ORDER_ID, labResult.patient_no, labTest.TestId, labTest.TestName, testRange.LowerLimit, testRange.UpperLimit }).Distinct(),
LabParaResult = db.LAB_PARA_RESULTS.Where(o => o.ORDER_ID == id).Include(t => t.Patients).ToList(),
LabCultureResults = db.LAB_CULTURE_RESULT.Where(o => o.ORDER_ID == id).ToList(),
LabMicroResults = db.LAB_MICRO_NEGATIVE_RESULT.Where(o => o.ORDER_ID == id).ToList(),
labCashView = db.LAB_RESULT_CASH_VIEW.Where(o => o.order_number == id).ToList(),
labtests = db.LabTests.ToList(),
patients = db.Patients.ToList()
};
但是我将如何在视图代码中调用它，而foreach循环却没有出现?
but how i will call it in view code and foreach loop its not appear ?
这是查看代码:
@model AljawdahNewSite.Models.Orders_Tables
@{
ViewBag.Title = "CashData";
Layout = "~/Views/Shared/_LayoutPatients.cshtml";
var hema = new List<int>() { 1 };
var bio = new List<int>() { 2 };
var ser = new List<int>() { 3 };
var hor = new List<int>() { 4 };
var culture = new List<int>() { 7 };
var para = new List<int>() { 6,9 };
var labPara = Model.LabParaResult.FirstOrDefault();
var labCult = Model.LabCultureResults.FirstOrDefault();
var labMicro= Model.LabMicroResults.FirstOrDefault();
var LabResults = Model.LabResults;
var labhema = LabResults.FirstOrDefault(x => x.deptid == 1);
var labBio = LabResults.FirstOrDefault(x => x.deptid == 2);
var labSer = LabResults.FirstOrDefault(x => x.deptid ==3);
var labHor = LabResults.FirstOrDefault(x => x.deptid == 4);
}
@if (labhema != null && labhema.APPROVED_DATE.HasValue)
{
<div class="tab-pane container active" id="hemadiv" >
<h5 class="text-uppercase p-2 text-center">Hematology Department</h5>
<table class="table table-bordered" cellpadding="5" cellspacing="5" border="1">
<thead>
<tr>
<th>@Html.DisplayNameFor(m => m.LabResults.FirstOrDefault().patient_no)</th>
<th>@Html.DisplayNameFor(m => m.patients.FirstOrDefault().Patient_Name)</th>
<th>@Html.DisplayNameFor(m => m.labtests.FirstOrDefault().TestName)</th>
<th>@Html.DisplayNameFor(m => m.LabResults.FirstOrDefault().RESULT_NUMBER)</th>
<th>@Html.DisplayNameFor(m => m.testsRanges.FirstOrDefault().LowerLimit)</th>
<th>@Html.DisplayNameFor(m => m.testsRanges.FirstOrDefault().UpperLimit)</th>
<th>@Html.DisplayNameFor(m => m.testsRanges.FirstOrDefault().text_range)</th>
<th>@Html.DisplayNameFor(m => m.LabResults.FirstOrDefault().APPROVED_DATE)</th>
</tr>
</thead>
@foreach (var employee in Model.LabResults.Where(x => hema.Contains(x.LabTests.Dept_id.GetValueOrDefault())))
{
<tr>
<td>@employee.patient_no</td>
<td>@employee.Patients.Patient_Name</td>
<td>@employee.LabTests.TestName</td>
<td>@employee.RESULT_NUMBER</td>
<td>@employee.result.LowerLimit</td> // how to call here
<td>@employee.result.UpperLimit</td> // how to call here
<td>@employee.result.text_range</td> // how to call here
<td>@employee.APPROVED_DATE</td>
</tr>
}
</table>
</div>
}`
` 我尝试了另一种将var结果放到var表之外并分配Labresult = result的方法:
` I tried another way to put the var result outside the var table and assign Labresult = result :
public ActionResult CashData(int id)
{
var result = (from labResult in db.LAB_RESULTS
join labTest in db.LabTests on labResult.TESTID equals labTest.TestId
join testRange in db.TestsRanges on labResult.TESTID equals testRange.TestId
where labResult.ORDER_ID == id
select new { labResult.ORDER_ID, labResult.patient_no, labTest.TestId, labTest.TestName, testRange.LowerLimit, testRange.UpperLimit }).Distinct(),
var tables = new Orders_Tables
{
testsRanges = db.TestsRanges.ToList(),
//LabResults = db.LAB_RESULTS.Where(o => o.ORDER_ID == id )
// .Include(p => p.LabTests)
// .Include(t => t.Patients).ToList(),
//LabResults = result,
LabResults = result,
LabParaResult = db.LAB_PARA_RESULTS.Where(o => o.ORDER_ID == id).Include(t => t.Patients).ToList(),
LabCultureResults = db.LAB_CULTURE_RESULT.Where(o => o.ORDER_ID == id).ToList(),
LabMicroResults = db.LAB_MICRO_NEGATIVE_RESULT.Where(o => o.ORDER_ID == id).ToList(),
labCashView = db.LAB_RESULT_CASH_VIEW.Where(o => o.order_number == id).ToList(),
labtests = db.LabTests.ToList(),
patients = db.Patients.ToList()
};
return View(tables);
}
但它在LabResults = result,
Cannot implicitly convert type System.Linq.IQueryable to System Collections.Generic.IEnumerable an explicit conversion exists are you missing a cast ?
实验室结果分类
public partial class LAB_RESULTS
{
public int ID { get; set; }
public int SAMPLE_ID { get; set; }
public int ORDER_ID { get; set; }
public int TESTID { get; set; }
public int GROUPID { get; set; }
public string NORMAL_RESULT { get; set; }
public int SAMPLE_STATUS { get; set; }
public string EXAMINED_BY { get; set; }
public Nullable<System.DateTime> EXAMINED_DATE { get; set; }
public string APPROVED_BY { get; set; }
public Nullable<System.DateTime> APPROVED_DATE { get; set; }
public string RESULT_NUMBER { get; set; }
public string RESULT_REPORT { get; set; }
public string RESULT_NOTE { get; set; }
public Nullable<int> packageid { get; set; }
public Nullable<int> machine_id { get; set; }
public Nullable<int> deptid { get; set; }
public Nullable<int> patient_no { get; set; }
public Nullable<int> custid { get; set; }
public string REQ_FORM_NO { get; set; }
public Nullable<int> serial { get; set; }
public string text { get; set; }
public string value { get; set; }
public string packagename { get; set; }
public string UPDATED_BY { get; set; }
public Nullable<System.DateTime> UPDATED_DATE { get; set; }
public Nullable<int> prio_id { get; set; }
public Nullable<int> update_count { get; set; }
public string REJECTED_BY { get; set; }
public Nullable<System.DateTime> REJECTED_DATE { get; set; }
public Nullable<int> REJECTED_REASON { get; set; }
public virtual LabTests LabTests { get; set; }
public virtual Patients Patients { get; set; }
}
}
Labtest类:
public partial class LabTests
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public LabTests()
{
this.Groups = new HashSet<Groups>();
this.LAB_CULTURE_RESULT = new HashSet<LAB_CULTURE_RESULT>();
this.LAB_HISTO_RESULT = new HashSet<LAB_HISTO_RESULT>();
this.LAB_MICRO_NEGATIVE_RESULT = new HashSet<LAB_MICRO_NEGATIVE_RESULT>();
this.Lab_Packages = new HashSet<Lab_Packages>();
this.LAB_PARA_RESULTS = new HashSet<LAB_PARA_RESULTS>();
this.LAB_RESULTS = new HashSet<LAB_RESULTS>();
this.LAB_SEMEN_ANALYSIS_RESULT = new HashSet<LAB_SEMEN_ANALYSIS_RESULT>();
this.LAB_URINE_ANALYSIS_RESULT = new HashSet<LAB_URINE_ANALYSIS_RESULT>();
this.TestsRanges = new HashSet<TestsRanges>();
}
public int TestId { get; set; }
public string TestName { get; set; }
public Nullable<int> Dept_id { get; set; }
public Nullable<int> Machine_id { get; set; }
public Nullable<int> TestType { get; set; }
public Nullable<int> UnitId { get; set; }
public Nullable<int> TestGender { get; set; }
public Nullable<int> TestAvailable { get; set; }
public Nullable<int> groupid { get; set; }
public Nullable<int> ID_sample { get; set; }
public Nullable<int> test_duration { get; set; }
public string TestNotes { get; set; }
public string group_name { get; set; }
public Nullable<int> culture { get; set; }
public Nullable<int> test_status { get; set; }
public virtual Departments Departments { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Groups> Groups { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<LAB_CULTURE_RESULT> LAB_CULTURE_RESULT { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<LAB_HISTO_RESULT> LAB_HISTO_RESULT { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<LAB_MICRO_NEGATIVE_RESULT> LAB_MICRO_NEGATIVE_RESULT { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Lab_Packages> Lab_Packages { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<LAB_PARA_RESULTS> LAB_PARA_RESULTS { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<LAB_RESULTS> LAB_RESULTS { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<LAB_SEMEN_ANALYSIS_RESULT> LAB_SEMEN_ANALYSIS_RESULT { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<LAB_URINE_ANALYSIS_RESULT> LAB_URINE_ANALYSIS_RESULT { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<TestsRanges> TestsRanges { get; set; }
public virtual TestsUnits TestsUnits { get; set; }
}
}
TestsRanges类
TestsRanges class
public partial class TestsRanges
{
public Nullable<int> TestId { get; set; }
public Nullable<int> CatId { get; set; }
public Nullable<int> PatientSex { get; set; }
public Nullable<int> CaseId { get; set; }
public string LowerLimit { get; set; }
public string UpperLimit { get; set; }
public int id { get; set; }
public int machine_id { get; set; }
public string text_range { get; set; }
public string panic_value { get; set; }
public virtual LabTests LabTests { get; set; }
}
LabTests表中的testid主键，其他表中的外键.
testid primary key in LabTests table and foreign key in the other tables.
推荐答案
要解决此类错误和大量关系，只需创建具有所有关系的SQL SERVER VIEW，然后将此视图作为一个表添加到模型视图和控制器中，无需创建任何额外的联接，并在其中包括一个呼叫，并将其包括在您的控制器中:
To solve such errors and big number of relations just create SQL SERVER VIEW with all relations then add this view to your model view and controllers as one table no need to create any extra join and where and include in your controller just one call:
CREATE VIEW [dbo].[LAB_RESULT_VIEW] AS
SELECT distinct [LAB_RESULTS].ORDER_ID
,LAB_RESULTS.patient_no
,Patients.PATIENT_NAME
,labtests.TestId
,labtests.TestName
,[RESULT_NUMBER]
,TestsRanges.LowerLimit
,TestsRanges.UpperLimit
,TestsUnits.UnitName
FROM [dbo].[LAB_RESULTS]
inner join LabTests on LabTests.testid=LAB_RESULTS.TESTID
inner join TestsRanges on TestsRanges.TestId = LAB_RESULTS.TESTID
inner join patients on Patients.Patient_No = LAB_RESULTS.patient_no
inner join machines on lab_results.machine_id = machines.machine_id
inner join gender on gender.genderid = Patients.Gender
inner join TestsUnits on TestsUnits.UnitId = LabTests.UnitId
inner join Lab_orders_Cash on Lab_orders_Cash.cash_order_id = LAB_RESULTS.ORDER_ID
inner join PatientCat on (patientcat.CatId = Lab_orders_Cash.CatId)
inner join Lab_Hematology_Samples on Lab_Hematology_Samples.SAMPLE_ID = LAB_RESULTS.SAMPLE_ID
where lab_results.ORDER_ID = 2000000058
and (Lab_orders_Cash.catid = TestsRanges.CatId )
and TestsRanges.machine_id = LAB_RESULTS.machine_id
and (Patients.Gender = TestsRanges.PatientSex or TestsRanges.PatientSex = 1 )
这篇关于使用多个联接方法语法将SQL SERVER SELECT转换为LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
