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

问题描述

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