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

问题描述

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

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());

    }

我无法将谓词转换为可用的sql查询.我得到一个例外，说它无法为谓词列表生成sql.我也尝试过

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();

虽然确实执行了问题，但生成的sql却拉下了整个表，并且根本不生成where子句.

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?

也在在C#中连接Lambda函数进行了尝试，但它也抛出无法生成sql查询的异常.

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

推荐答案

如果您有新的List<Expression<System.Func<table, bool>>>而不是新的List<System.Func<table, bool>>，则可以执行类似的操作

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;
}

这篇关于Linq到自定义SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！