請教各位大大,我參考JOIN的程式碼如下~
List<UnSignListVModel> sv = new List<UnSignListVModel>();
List<UnSignListVModel> sv2 = new List<UnSignListVModel>();
sv = db.RepairFlows.Where(f => f.Status == "?")
.Join(db.RepairDtls, f => f.DocId, rd => rd.DocId,
(f, rd) => new
{
f.DocId,
f.UserId,
f.Cls,
rd.Cost,
rd.EndDate,
rd.CloseDate,
rd.FailFactor,
rd.DealDes,
rd.DealState
})
.Join(db.Repairs, rd => rd.DocId, k => k.DocId,
(rd, k) => new
{
rd.DocId,
rd.UserId,
rd.Cls,
k.AccDpt,
k.ApplyDate,
k.AssetNo,
rd.Cost,
rd.EndDate,
rd.CloseDate,
rd.FailFactor,
rd.DealDes,
rd.DealState,
k.TroubleDes
}).Where(k => k.ApplyDate >= v.Sdate && k.ApplyDate <= v.Edate)
.Join(db.Assets, k => k.AssetNo, at => at.AssetNo,
(k, at) => new
{
k.DocId,
k.UserId,
k.Cls,
k.AccDpt,
k.ApplyDate,
k.AssetNo,
at.Cname,
k.Cost,
k.EndDate,
k.CloseDate,
k.TroubleDes,
k.FailFactor,
k.DealDes,
k.DealState,
at.Type,
at.AssetClass
})
.Join(db.Departments, k => k.AccDpt, c => c.DptId,
(k, c) => new
{
k.DocId,
k.UserId,
k.Cls,
k.AccDpt,
k.ApplyDate,
k.AssetNo,
k.Cname,
k.Cost,
k.EndDate,
k.CloseDate,
k.TroubleDes,
k.FailFactor,
k.DealDes,
k.DealState,
k.Type,
k.AssetClass,
c.Name_C
})
.Join(db.AppUsers, k => k.UserId, u => u.Id,
(k, u) => new UnSignListVModel
{
DocTyp = "請修",
DocId = k.DocId,
AccDpt = k.AccDpt,
AccDptNam = k.Name_C,
AssetNo = k.AssetNo,
AssetName = k.Cname,
Type = k.Type,
ApplyDate = k.ApplyDate.Value,
EndDate = k.EndDate,
TroubleDes = k.TroubleDes,
FailFactor = k.FailFactor,
DealDes = k.DealDes,
DealState = k.DealState,
EngNam = null,
ClsEmp = u.FullName,
AssetClass = k.AssetClass
}).ToList();
其中不懂的是,為什麼每一段JOIN都要new那麼多東西出來,例如:DocId每一段JOIN都要new出來,這是一定要這樣寫嗎?有沒有更簡潔的寫法?
前面join與new出來的參數應該是為了在最後面輸出時可以接著呼叫用,是說每一段都new同樣的東西這是一定要的嗎?
我參考這樣的寫法,寫了一段如下:
List<MemberViewModel> mv = new List<MemberViewModel>();
db.MemberToPapers.Where(x => x.ConferenceId == ConferenceId && x.PaperId == PaperId && x.Role == Role)
.Join(db.Members,
m => m.MemberId,
x => x.MemberId,
(m, x) => new
{
m.ConferenceId,
m.PaperId,
x.MemberId,
x.Name,
x.Email,
m.Role
}).ToList()
.ForEach(o => mv.Add(new MemberViewModel
{
ConferenceId = o.ConferenceId,
PaperId = o.PaperId,
MemberId = o.MemberId,
Name = o.Name,
Email = o.Email,
RoleName = GetRole(o.Role)
}));
我在想說(m, x)也可以改寫成如下:
(m, x) => new
{
m,
x
}).ToList()
或是(m, x)可以合併成一個變數輸出即可?這樣是不是比較簡潔?
另外一問,我參考以下這一篇文章,想要做所謂的Dynamic LINQ:
http://kevintsengtw.blogspot.com/2012/06/dynamic-linq-entity-framework-part3.html
public ActionResult Test1()
{
using (NorthwindEntities db = new NorthwindEntities())
{
var query = db.Customers
.Where("City == @0 and Orders.Count >= @1", "London", 10)
.OrderBy("CustomerID desc")
.Take(10);
ViewData.Model = query.ToList();
return View();
}
}
然後我就把我的程式改寫成以下
db.MemberToPapers.Where("ConferenceId == @0 AND PaperId == @1 AND Role == @2", ConferenceId, PaperId, Role)
.Join(db.Members,
m => m.MemberId,
x => x.MemberId,
(m, x) => new
{
m.ConferenceId,
m.PaperId,
x.MemberId,
x.Name,
x.Email,
m.Role
}).ToList()
.ForEach(o => mv.Add(new MemberViewModel
{
ConferenceId = o.ConferenceId,
PaperId = o.PaperId,
MemberId = o.MemberId,
Name = o.Name,
Email = o.Email,
RoleName = GetRole(o.Role)
}));
VS回報錯誤:'方法'Where'沒有任何多載使用4個引數'
不知道要如何修正才能改為Dynamic LINQ呢?
其中不懂的是,為什麼每一段JOIN都要new那麼多東西出來,例如:DocId每一段JOIN都要new出來,這是一定要這樣寫嗎?有沒有更簡潔的寫法?
不用每次都new,有更簡潔寫法
個人習慣,多層LINQ join我會比較少使用Lambda LINQ
以經典的NorthWind資料庫為例,Order跟Employee、Customer Join查詢資料,我會寫成這樣:
from t1 in db.Orders
join t2 in db.Employees on t1.EmployeeID equals t2.EmployeeID
join t3 in db.Customers on t1.CustomerID equals t3.CustomerID
where t1.EmployeeID == 5
select new {t1.CustomerID,t1.EmployeeID,t2.FirstName,t2.LastName,t3.CompanyName}
個人覺得這樣貼近SQL並且語義也好理解
前面join與new出來的參數應該是為了在最後面輸出時可以接著呼叫用,是說每一段都new同樣的東西這是一定要的嗎?
不,最後在new
最後建議標題改為 LINQ Join 可否不多次 Select new ?
可以參考StackOverflow作法,後面兩個問題另開問題,一個問題只解決一個案子
,這樣標題能比較明確也不會混淆,方便後面有同樣問題的版友查找。
給大大你參考 ^___^
Join 很多表示 schema design 沒有善用 foreign key,LINQ 就可以用 main_table.child_table.field_name 方式 access,個人建議應該在 DB 將 ER Diagram 整理出來,再設定 foreign key,之後,利用 『code first from database』,寫程式就會比較輕鬆了。
不愛用Lambda Linq的話,可以回頭用SQL Command,可讀性及速度都比Linq好一些
先做一個裝資料的Entitiy
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DataClasses1DataContext db = new DataClasses1DataContext();
List<MyObj> mydatalist = new List<MyObj>(); //在最在方有宣告這個myobj的entity
// ↓↓↓↓↓↓你自己想要動態組裝的SQL
string asql = @"
SELECT A.* , Buyer.name as BuyerName , employee.name as SalesName
FROM sellrecord as A
Inner Join Buyer on Buyre.id = A.buyerid
Inner Join employee on employee = A.salesid
WHERE a.dealdate between '2019/07/01' and '2019/07/31' ";
//↓↓↓↓↓↓直接抓datacontext的connectstring就可以
SqlConnection sqlconn = new SqlConnection(db.Connection.ConnectionString);
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand(asql, sqlconn);
SqlDataReader sdr = sqlcomm.ExecuteReader();
while (sdr.Read())
{
mydatalist.Add(new MyObj()
{
pono = (string)sdr["pono"],
id = (int)sdr["id"],
buyername = (string)sdr["buyername"],
empname = (string)sdr["salesname"],
});
}
if (mydatalist.Any())
MessageBox.Show(mydatalist.First().pono);
// what todo..........
}
}
public class MyObj
{
public int id { get; set; }
public string pono { get; set; }
public string buyername { get; set; }
public string salesname { get; set; }
public detain pd_itemspec { get;set; }
public decimal price {get;set;}
//.....................
}
}