How do I filter a linq query?

This is how you do a left outer join in LINQ, kind of weird, but it works.

 join d in TableA
    on TableB.Key equals d.Key
    into joinedItems
    from joinedItemin joinedItems.DefaultIfEmpty()  

For the dates, I typically use predicatebuilder to generate my query parameters, more info on that can be found here:

http://www.albahari.com/nutshell/predicatebuilder.aspx

Thanks for the link.

I'm actually hoping to implement a linq query that returns data filtered on entered keywords. What I have at the moment is a drop down list (ddlFilter) and a textbox (txtFilter) on my frontend placed just above my listview which allows a user to select a specific database column from my main table (Trusts) and then enter keywords for filtering my listviews data to only that which contains the keyword searched upon within the selected column from the drop down. Within the OnSelecting event of the linqdatasource bound to my listview I have written the attached query but unfortunately the results aren't as accurate as I would like as all data seems to be returned, not the data specific to the chosen column from the drop down. Would the predicatebuilder help me return more accurate results? I'm not too sure where to place the IQueryable method? Am I right in placing my PredicateBuilder public static class before my pages public partial class?

Any help would be appreciated.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using AjaxControlToolkit;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Linq.Mapping;
using System.Linq.Expressions;
using System.Reflection;
using System.Data.SqlClient;
 
public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }
 
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }
 
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}
 
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }
 
    protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        TrustSystemDataContext db = new TrustSystemDataContext();
 
        string propertyName = string.Empty;
        string propertyValue = string.Empty;
        if (!string.IsNullOrEmpty(this.txtFilter.Text))
        {
            propertyName = this.ddlFilter.SelectedValue;
            propertyValue = this.txtFilter.Text;
        }
 
        var query = from tt in db.Trusts
                    join felt in db.FeeExecLookups on tt.FeeExecCode equals felt.FeeExecCode
                    join ilt in db.IncomeLookups on tt.IncomeCode equals ilt.IncomeCode
                    join plt in db.PartnerLookups on tt.PartnerCode equals plt.PartnerCode
                    join tlt in db.TypeLookups on tt.TypeCode equals tlt.TypeCode
                    where (tt.ClientName.Contains(propertyValue) || tt.MatterNo.Contains(propertyValue) || tt.PartnerLookup.PartnerDescription.Contains(propertyValue) || tt.FeeExecLookup.FeeExecDescription.Equals(propertyValue) || tt.TypeLookup.TypeDescription.Contains(propertyValue))
                    orderby tt.ClientName ascending
                    select new { tt.TrustID, tt.ClientName, tt.MatterNo, tt.PartnerCode, tt.FeeExecCode, tt.TypeCode, tt.TrustTypeCode, tt.Nominee, tt.TEA, tt.IncomeCode, tt.TrustNotes, tt.Archive, felt.FeeExecDescription, ilt.IncomeDescription, plt.PartnerDescription, tlt.TypeDescription };
 
        e.Result = query;
    }
}

Select allOpen in new window

I don't understand what these lines do?...

predicate = predicate.Or(x => x.column.StartsWith("woo"));
predicate = predicate.Or(x => x.column2.StartsWith("woo"));
predicate = predicate.Or(x => x.column3.StartsWith("woo"));

I don't seem to get access to my column names from my code behind when I attempt the above? Do you have any suggestions on how I would use my propertyName and propertyValue variables as I need to pull back data that contains the keywords that the user types into my propertyValue variable...?

Ok, I have now managed to use my columns, I was referring to the wrong class.

What I have now within my linqdatasource OnSelecting event is attached...

No errors are thrown but due to removing my where filters and replacing them with the "predicate.Or" statements (is this correct?) my data no longer filters? I'm not too sure how I am meant to use the predicatebuilder to filter my data on what the user types into my frontend textbox (txtFilter)?

protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        TrustSystemMARTINSPALDINGDataContext db = new TrustSystemMARTINSPALDINGDataContext();
 
        string propertyName = string.Empty;
        string propertyValue = string.Empty;
 
        if (!string.IsNullOrEmpty(this.txtFilter.Text))
        {
            propertyName = this.ddlFilter.SelectedValue;
            propertyValue = this.txtFilter.Text;
        }
 
        var predicate = LinqPredicateBuilder.True<Trust>();
        predicate = predicate.Or(x => x.ClientName.Contains(propertyValue));
        predicate = predicate.Or(x => x.MatterNo.Contains(propertyValue));
 
        var query = (from tt in db.Trusts.Where(predicate)
                     join felt in db.FeeExecLookups on tt.FeeExecCode equals felt.FeeExecCode
                     join ilt in db.IncomeLookups on tt.IncomeCode equals ilt.IncomeCode
                     join plt in db.PartnerLookups on tt.PartnerCode equals plt.PartnerCode
                     join tlt in db.TypeLookups on tt.TypeCode equals tlt.TypeCode
                     // perform a left join on TrustTypeLookups so all fields are returned including NULLs
                     join ttlt in db.TrustTypeLookups on tt.TrustTypeCode equals ttlt.TrustTypeCode into tc
                     from ttlt in tc.DefaultIfEmpty()
                     orderby tt.ClientName ascending
                     // TrustTypeDescription is selected on different cases below: whether it's null, nothing or something
                     select new { tt.TrustID, tt.ClientName, tt.MatterNo, tt.PartnerCode, tt.FeeExecCode, tt.TypeCode, tt.TrustTypeCode, tt.Nominee, tt.TEA, tt.IncomeCode, tt.TrustNotes, tt.Archive, felt.FeeExecDescription, ilt.IncomeDescription, plt.PartnerDescription, tlt.TypeDescription, TrustTypeDescription = ttlt.TrustTypeDescription == null ? "" : ttlt.TrustTypeDescription });
 
        e.Result = query;
}

Select allOpen in new window

Sorry for yet another response but I think I'm getting there :)

I think I have now managed to instantiate the builder and create a class that will perform the filtering I need for my listview (code attached) but what I'm struggling to do now is call the class within the OnSelecting event of my linqdatasource? Any ideas?

Thanks.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
 
/// <summary>
/// Summary description for LinqPredicateBuilder
/// </summary>
public static class LinqPredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }
 
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }
 
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}
 
public partial class Trust
{
    public static Expression<Func<Trust, bool>> ContainsInDescription(params string[] keywords)
    {
        TrustSystemMARTINSPALDINGDataContext db = new TrustSystemMARTINSPALDINGDataContext();
        
        var predicate = LinqPredicateBuilder.True<Trust>();
        foreach (string keyword in keywords)
        {
            string temp = keyword;
            predicate = predicate.Or(p => p._ClientName.Contains(temp));
            predicate = predicate.Or(p => p._MatterNo.Contains(temp));
            predicate = predicate.Or(p => p._PartnerLookup.Entity.PartnerDescription.Contains(temp));
            predicate = predicate.Or(p => p._FeeExecLookup.Entity.FeeExecDescription.Contains(temp));
            predicate = predicate.Or(p => p._TypeLookup.Entity.TypeDescription.Contains(temp));
 
            var query = (from tt in db.Trusts.Where(predicate)
                         join felt in db.FeeExecLookups on tt.FeeExecCode equals felt.FeeExecCode
                         join ilt in db.IncomeLookups on tt.IncomeCode equals ilt.IncomeCode
                         join plt in db.PartnerLookups on tt.PartnerCode equals plt.PartnerCode
                         join tlt in db.TypeLookups on tt.TypeCode equals tlt.TypeCode
                         // perform a left join on TrustTypeLookups so all fields are returned including NULLs
                         join ttlt in db.TrustTypeLookups on tt.TrustTypeCode equals ttlt.TrustTypeCode into tc
                         from ttlt in tc.DefaultIfEmpty()
                         orderby tt.ClientName ascending
                         // TrustTypeDescription is selected on different cases below: whether it's null, nothing or something
                         select new { tt.TrustID, tt.ClientName, tt.MatterNo, tt.PartnerCode, tt.FeeExecCode, tt.TypeCode, tt.TrustTypeCode, tt.Nominee, tt.TEA, tt.IncomeCode, tt.TrustNotes, tt.Archive, felt.FeeExecDescription, ilt.IncomeDescription, plt.PartnerDescription, tlt.TypeDescription, TrustTypeDescription = ttlt.TrustTypeDescription == null ? "" : ttlt.TrustTypeDescription });
        }
        return predicate;
    }
}

Select allOpen in new window

I have now solved my problem. I moved the code within my "public partial class trust" to my linqdatasources "OnSelecting" event and added a few if conditions upon my propertyName variable which now runs through and performs my search as I would expect....few!