iT邦幫忙

0

ASP.NET MVC Dynamic LINQ

我參考以下這一篇文章,想要做所謂的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();
    }
}

然後我就把我的程式改寫成以下

public IPagedList<MemberViewModel> IPGetMemberToPaperRole(int ConferenceId, int PaperId, string Role, int? Page, string SearchKeyWord = "")
{
    List<MemberViewModel> mv = new List<MemberViewModel>();
    db.MemberToPapers.Where("ConferenceId == @0 AND PaperId == @1 AND Role == @2", ConferenceId, PaperId, Role)
        .Join(db.Members.Where(wm => wm.Name.Contains(@3), SearchKeyWord),
        m => m.MemberId,
        x => x.MemberId,
        (m, x) => new
        {
            m.ConferenceId,
            m.PaperId,
            m.Role,
            x.MemberId,
            x.Name,
            x.Email
        }).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)
        }));
    int pageNumber = (Page ?? 1);
    int pageSize = 2;
    return mv.ToPagedList(pageNumber, pageSize);
}

VS回報錯誤:'方法'Where'沒有任何多載使用4個引數'
不知道要如何修正才能改為Dynamic LINQ呢?
我想要做的是若SearchKeyWord有值,則搜尋出KeyWord相關的資料,若SearchKeyWord沒值,則搜尋出全部的資料。

1 個回答

1
暐翰
iT邦大師 1 級 ‧ 2019-07-30 15:12:34
最佳解答

VS回報錯誤:'方法'Where'沒有任何多載使用4個引數'
不知道要如何修正才能改為Dynamic LINQ呢?

這是缺少套件原因,Dynamic LINQ是第三方NuGet套件,你需要安裝

安裝完後using就可以使用了

using System.Linq.Dynamic;

效果圖:
20190730151421.png


補充

dynamic LINQ專案是開源的,可以看一下它使用的是DynamicExpression技術
20190730151531.png
所以有一個很重要概念它不只能用在LINQ SQL,普通的集合物件LINQ Object也可以使用,這是一個很強大的功能,舉例:

	var datas = new[] { new { id = 1, value = "001" }, new { id = 2, value = "002" } };
	var result = datas.AsQueryable().Where("id=@0", 1);
	Console.WriteLine(result);

20190730152708.png

看更多先前的回應...收起先前的回應...
fillano iT邦超人 1 級 ‧ 2019-07-30 15:25:50 檢舉

不過我覺得他的需求似乎並不需要Dynamic Linq。只要mv維持是IQueryable,然後根據條件再加入Where就好了,查詢並不需要一次做完。

IQueryable要等到Materialize時才會產生sql去跟資料庫查詢。(例如:呼叫ToList()方法等的時候)

暐翰 iT邦大師 1 級 ‧ 2019-07-30 15:32:25 檢舉

fillano大大是的,他不需要使用到Dynamic LINQ,而且先熟悉強類型的LINQ更重要
其實想藉由他的提問跟大家介紹Dynamic LINQ不只能用在LINQ SQL的強大功能

leo226 iT邦新手 5 級 ‧ 2019-07-31 12:29:34 檢舉

如大大建議,是不用Dynamic Linq,改天再試做Dynamic Linq,不過目前改寫的內容只能用最笨的寫法如下:

if (!string.IsNullOrEmpty(SearchKeyWord))
{
    db.MemberToPapers.Where(x => x.ConferenceId == ConferenceId && x.PaperId == PaperId && x.Role == Role)
    .Join(db.Members.Where(wm => wm.Name.Contains(SearchKeyWord)),
    m => m.MemberId,
    x => x.MemberId,
    (m, x) => new
    {
        m.ConferenceId,
        m.PaperId,
        m.Role,
        x.MemberId,
        x.Name,
        x.Email
    }).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)
    }));
}
else
{
    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,
        m.Role,
        x.MemberId,
        x.Name,
        x.Email
    }).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)
    }));
}

嘗試著用fillano大大建議的方法,遇到許多轉型的問題。。。><

var result = db.MemberToPapers.Where(x => x.ConferenceId == ConferenceId && x.PaperId == PaperId && x.Role == Role);
if (!string.IsNullOrEmpty(SearchKeyWord))
{
    result = result.Join(db.Members.Where(wm => wm.Name.Contains(SearchKeyWord)),
    m => m.MemberId,
    x => x.MemberId,
    (m, x) => new
    {
        m.ConferenceId,
        m.PaperId,
        m.Role,
        x.MemberId,
        x.Name,
        x.Email
    });
}
else
{
    result = result.Join(db.Members,
    m => m.MemberId,
    x => x.MemberId,
    (m, x) => new
    {
        m.ConferenceId,
        m.PaperId,
        m.Role,
        x.MemberId,
        x.Name,
        x.Email
    });
}
result.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)
}));
暐翰 iT邦大師 1 級 ‧ 2019-07-31 16:59:51 檢舉

leo226 這段真的有重複代碼的壞味道

fillano iT邦超人 1 級 ‧ 2019-07-31 17:58:07 檢舉

leo226 先Join db.Members,然後where下在if{}裡面啦。Don't repeat yourself。

leo226 iT邦新手 5 級 ‧ 2019-08-01 09:47:51 檢舉

fillano大大,其實先Join db.Members,然後where下在if{}裡面我知道,也很想,但改寫方式一直遇到錯誤,實在不知道該如何改,才只能用最爛的方式寫了。。。><
如果大大方便的話是否可以給我一個小小範例,真的感激不盡~

var result = db.MemberToPapers.Where(x => x.ConferenceId == ConferenceId && x.PaperId == PaperId && x.Role == Role).Join(db.Members);
if (!string.IsNullOrEmpty(SearchKeyWord))
{
    result = result.Where(wm => wm.Name.Contains(SearchKeyWord))
}
fillano iT邦超人 1 級 ‧ 2019-08-01 14:56:46 檢舉

leo226 我用物件模擬你的資料:
https://dotnetfiddle.net/ISIiOl

你可以參考看看。

leo226 iT邦新手 5 級 ‧ 2019-08-01 16:33:58 檢舉

大大真的太用心了,我只要關鍵的小小範例,大大整個資料都模擬出來,我改寫如下測試OK了,非常感謝fillano花那麼多時間幫我解答,也感謝暐翰大師~謝謝~

var result = 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,
        m.Role,
        x.MemberId,
        x.Name,
        x.Email
    });
if (!string.IsNullOrEmpty(SearchKeyWord))
{
    result = result.Where(wm => wm.Name.Contains(SearchKeyWord));
}
result.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)
}));

我要發表回答

立即登入回答