[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1243
  • Last Modified:

How do I filter a linq query?

I have written a C# web site using visual studio 2008. I am using an asp.net 3.5 listview control to display my data. I have two textboxes linked to ajax calendar extenders to produce datetime values along with a button on my listview frontend. My idea is that upon the user entering dates (a from and to date) into my textboxes they click a button which will in turn return filtered data depending on their date ranges. I have written the linq select query (attached) which involves a few joins (mainly to lookup tables) but the query only returns data when there is data present in all my main tables (Trusts, TrustCrucialDates, TrustPayments). I was hoping to perform a left or outer join so that if there is no data in either my TrustCrucialDates table or TrustPayments table the query will not break and return no data but instead return data with null values, unfortunately I am not sure how to perform this in linq? My database diagram is attached also.
var query = from tt in db.Trusts
                        join ttl in db.TypeLookups on tt.TypeCode equals ttl.TypeCode
                        join tfel in db.FeeExecLookups on tt.FeeExecCode equals tfel.FeeExecCode
                        join tpl in db.PartnerLookups on tt.PartnerCode equals tpl.PartnerCode
                        join cdtcd in db.TrustCrucialDates on tt.TrustID equals cdtcd.TrustID
                        join cdel in db.EventLookups on cdtcd.EventCode equals cdel.EventCode
                        // peforms 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()
                        where (tt.ClientName.Contains(propertyValue) || tt.MatterNo.Contains(propertyValue) || tt.PartnerLookup.PartnerDescription.Contains(propertyValue) || tt.FeeExecLookup.FeeExecDescription.Contains(propertyValue) || tt.TypeLookup.TypeDescription.Contains(propertyValue))
                        orderby cdtcd.DueDate ascending
                        select new { tt.TrustID, tt.ClientName, tt.MatterNo, tpl.PartnerDescription, tfel.FeeExecDescription, ttl.TypeDescription, tt.TrustNotes, cdtcd.CrucialNotes, cdtcd.DueDate, cdel.EventDescription, TrustTypeDescription = ttlt.TrustTypeDescription == null ? "" : ttlt.TrustTypeDescription };

Open in new window

database.jpg
0
Shepwedd
Asked:
Shepwedd
  • 5
  • 2
1 Solution
 
DavidTMooreCommented:
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
0
 
ShepweddAuthor Commented:
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;
    }
}

Open in new window

0
 
DavidTMooreCommented:
Typically this is how I do it:

Hope this helps.
var predicate = PredicateBuilder.True<table>();
predicate = predicate.Or(x => x.column.StartsWith("woo"));
predicate = predicate.Or(x => x.column2.StartsWith("woo"));
predicate = predicate.Or(x => x.column3.StartsWith("woo"));
 
var query = (from ex in db.table.Where(predicate)
join...
select new...

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
ShepweddAuthor Commented:
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...?
0
 
ShepweddAuthor Commented:
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;
}

Open in new window

0
 
ShepweddAuthor Commented:
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;
    }
}

Open in new window

0
 
ShepweddAuthor Commented:
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!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now