Linq到自定义SQL[英] Linq to custom sql

本文是小编为大家收集整理的关于Linq到自定义SQL的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

好吧,我有一个带有巨大桌子的DB.超过100万记录和超过50列. (我知道它不是最佳的,而是我必须使用的),因此我需要运行限制返回数据量的查询.现在我的问题是这个.我有一些可以运行并返回数据的自定义查询.用户可以通过选择过滤器和将生成谓词模板并将其应用于列表的选项通过选择过滤器和选项来进行更多的过滤数据.我现在需要获取谓词列表,并将其组合并重新查询数据库以搜索(更改或其他条目匹配).问题是这个

private Func<table,bool> filterAll()
    {

        Func<table, bool> temp = null;
        var list = mylist.filterList; //filterlist is a list<Predicate<table>>
        var list2 = list.Select(val => val.Value).ToArray();

        foreach(var a in list2)
        {

            temp += t => a(t);
        }
        return temp;
    }

    private void loadWithFiltersButton_Click(object sender, EventArgs e)
    {


        var temp = db.table.Where(filterAll());

    }

我不能将谓词变成可用的SQL查询.我得到一个例外,说它无法为谓词列表生成SQL.我也尝试了这个

Func<table, bool> query1 = temp2 => temp2.Name.Contains("test string");
Func<table, bool> query2 = temp2 => temp2.ignore == false;
var temp = db.table.Where(query1);
var myval = temp.Where(temp2 => temp2.Name.Contains("test string")).Select(val => val).ToList();

虽然执行问题是SQL生成的sql下拉整个表,并且根本不会生成一个条款.

我一直在搜索,并找到了此 https://stackoverflow.com/questions/974719/how-to-to-use-linq-to-compile-a-a-lambda-expression-to-custom-sql-sql-or-otherwise 但是答案中人们发布的所有链接对我来说都是死的.

基本上,我如何将多个谓词组合到可用的查询中,该查询将使数据库返回最少的数据量?

编辑:也尝试了此操作 conventeNating lambda函数在C#中也是如此引发了无法生成SQL查询的例外.

推荐答案

尝试使用

SqlMethods.Like(temp2.Name, "test string")

而不是:

temp2.Name.Contains("test string")

为了这样做,您必须添加参考:

System.Data.Linq.SqlClient

其他推荐答案

如果您有新的List<Expression<System.Func<table, bool>>>而不是新的List<System.Func<table, bool>>,那么您可以做

之类的事情
private void Test()
{
   var list = new List<Expression<System.Func<table, bool>>>();

    Expression<Func<table, bool>> query1 = temp2 => temp2.Name.Contains("test string");
    Expression<Func<table, bool>> query2 = temp2 => temp2.ignore == false;

    list.Add(query1);
    list.Add(query2);

    var temp = filterAll(list).ToList();
}


private System.Linq.IQueryable<table> filterAll(List<Expression<Func<table, bool>>> list2 )
{

    var query = table.AsQueryable();
    foreach (var a in list2)
    {
        query = query.Where(a);
    }
    return query;
}

本文地址:https://www.itbaoku.cn/post/1556764.html

问题描述

Okay so I have a db with a HUGE table. Over 1million records and over 50 columns. (I know its not optimal but its what I have to work with) So I need to run queries that limit the amount of data returned. Now my problem is this. I have some custom queries that run and return data. The user can filter that data down more by selecting filter and options that will generate a Predicate template and applies it to the list. I now need to take the list of predicates and Combine them and re-query the db to search for (changes or other entries matching). The problem is this

private Func<table,bool> filterAll()
    {

        Func<table, bool> temp = null;
        var list = mylist.filterList; //filterlist is a list<Predicate<table>>
        var list2 = list.Select(val => val.Value).ToArray();

        foreach(var a in list2)
        {

            temp += t => a(t);
        }
        return temp;
    }

    private void loadWithFiltersButton_Click(object sender, EventArgs e)
    {


        var temp = db.table.Where(filterAll());

    }

I cant turn the predicates into a usable sql query. I get an exception saying it is unable to generate the sql for the list of Predicates. I have also tried this

Func<table, bool> query1 = temp2 => temp2.Name.Contains("test string");
Func<table, bool> query2 = temp2 => temp2.ignore == false;
var temp = db.table.Where(query1);
var myval = temp.Where(temp2 => temp2.Name.Contains("test string")).Select(val => val).ToList();

And while that does execute the problem is the sql generated pull down the whole table and doesnt generate a where clause at all.

I have been searching and found this https://stackoverflow.com/questions/974719/how-to-use-linq-to-compile-a-lambda-expression-to-custom-sql-or-otherwise but all the links people posted in the answers are dead for me.

So basicly how can I combine multiple predicates into a usable query that will have the db return the least amount of data possible?

Edit: Tried this also Concatenating Lambda Functions in C# but it also throws the exception that sql query cannot be generated.

推荐答案

Try using

SqlMethods.Like(temp2.Name, "test string")

instead of:

temp2.Name.Contains("test string")

In order to do so, you must add the reference:

System.Data.Linq.SqlClient

其他推荐答案

If you have a new List<Expression<System.Func<table, bool>>> rather than a new List<System.Func<table, bool>> then you can do something like

private void Test()
{
   var list = new List<Expression<System.Func<table, bool>>>();

    Expression<Func<table, bool>> query1 = temp2 => temp2.Name.Contains("test string");
    Expression<Func<table, bool>> query2 = temp2 => temp2.ignore == false;

    list.Add(query1);
    list.Add(query2);

    var temp = filterAll(list).ToList();
}


private System.Linq.IQueryable<table> filterAll(List<Expression<Func<table, bool>>> list2 )
{

    var query = table.AsQueryable();
    foreach (var a in list2)
    {
        query = query.Where(a);
    }
    return query;
}