Simple example for Linq predicates (multiple tables)

Everything you need to know is here (and how to download LinqKit.dll for Entity Framework) http://www.albahari.com/nutshell/predicatebuilder.aspx but I thought I would give my simple example and hopefully it clears any confusion up for you.

In LinqPad I did exactly the following:

1) Added a connection to my Entity Framework dll to use in the query

2) clicked f4 in query window

3) Found LinqKit.dll and added it as a reference

4) Checked the “Include PredicateBuilder”

5) Clicked “Additional Namespace Imports”

6) Typed “LinqKit”

7) Clicked “Set as default for new queries”

8) Changes Language to C# Program

9) wrote query below which uses predicates..

   1: void Main()

   2: {

   3:     var prs = SearchProducts("1", "", "", "");

   4:     

   5:     prs.Dump();

   6: }

   7:  

   8: IQueryable<ProductReturn> SearchProducts (string returnNumber, string serial, string accessCard, string trackingNumber)

   9: {

  10:   var predicate = PredicateBuilder.False<ProductReturn>();

  11:   

  12:   if(String.IsNullOrWhiteSpace(returnNumber) && String.IsNullOrWhiteSpace(serial) && String.IsNullOrWhiteSpace(accessCard) && String.IsNullOrWhiteSpace(trackingNumber))

  13:   {

  14:     predicate = PredicateBuilder.True<ProductReturn>();        

  15:   }

  16:   else

  17:   {

  18:  

  19:         if(!String.IsNullOrWhiteSpace(returnNumber)) predicate = predicate.Or(pr1 => pr1.ProductReturnIDFull.Contains(returnNumber));  

  20:         if(!String.IsNullOrWhiteSpace(serial)) predicate = predicate.Or(le1 => le1.LinesExpecteds.Any(m => m.Serial.Contains(serial)));  

  21:         if(!String.IsNullOrWhiteSpace(accessCard)) predicate = predicate.Or(le2 => le2.LinesExpecteds.Any(m => m.AccessCard.Contains(accessCard)));

  22:         if(!String.IsNullOrWhiteSpace(trackingNumber)) predicate = predicate.Or(le3 => le3.LinesExpecteds.Any(m => m.PrepaidLabelTracking.Contains(trackingNumber)));  

  23:     }    

  24:  

  25:   return ProductReturns.AsExpandable().Where (predicate);

  26: }

  27: // Define other methods and classes here

select sql generated in LinqPad 4 below with parameter for returnNumber and serial only.

   1: -- Region Parameters

   2: DECLARE p__linq__0 NVarChar(1) SET p__linq__0 = '%1%'

   3: DECLARE p__linq__1 NVarChar(1) SET p__linq__1 = '%a%'

   4: -- EndRegion

   5: SELECT 

   6: [Extent1].[ProductReturnID] AS [ProductReturnID], 

   7: [Extent1].[ProductReturnIDFull] AS [ProductReturnIDFull], 

   8: [Extent1].[ReturnStatusID] AS [ReturnStatusID], 

   9: [Extent1].[ReturnSystemStatusID] AS [ReturnSystemStatusID], 

  10: [Extent1].[CustID] AS [CustID], 

  11: [Extent1].[ContactName] AS [ContactName], 

  12: [Extent1].[ContactPhone] AS [ContactPhone], 

  13: [Extent1].[ContactEmail] AS [ContactEmail], 

  14: [Extent1].[ShipFromAddress1] AS [ShipFromAddress1], 

  15: [Extent1].[ShipFromAddress2] AS [ShipFromAddress2], 

  16: [Extent1].[ShipFromCity] AS [ShipFromCity], 

  17: [Extent1].[ShipFromState] AS [ShipFromState], 

  18: [Extent1].[ShipFromZipCode] AS [ShipFromZipCode], 

  19: [Extent1].[ReturnWarehouse] AS [ReturnWarehouse], 

  20: [Extent1].[PrepaidLabelCount] AS [PrepaidLabelCount], 

  21: [Extent1].[PrepaidLabelPDF] AS [PrepaidLabelPDF], 

  22: [Extent1].[PalletCountCarrier] AS [PalletCountCarrier], 

  23: [Extent1].[PalletCountTotalReturn] AS [PalletCountTotalReturn], 

  24: [Extent1].[PickUpDate] AS [PickUpDate], 

  25: [Extent1].[PickUpTime] AS [PickUpTime], 

  26: [Extent1].[IsLiftGateRequired] AS [IsLiftGateRequired], 

  27: [Extent1].[IsShrinkWrapRequired] AS [IsShrinkWrapRequired], 

  28: [Extent1].[IsPalletJackRequired] AS [IsPalletJackRequired], 

  29: [Extent1].[IsRestockingFee] AS [IsRestockingFee], 

  30: [Extent1].[RestockingFeeWaivedComment] AS [RestockingFeeWaivedComment], 

  31: [Extent1].[TotalPrepaidWeight] AS [TotalPrepaidWeight], 

  32: [Extent1].[TotalWeight] AS [TotalWeight], 

  33: [Extent1].[UpdateCounter] AS [UpdateCounter], 

  34: [Extent1].[Active] AS [Active], 

  35: [Extent1].[CreateDate] AS [CreateDate], 

  36: [Extent1].[CreatedBy] AS [CreatedBy], 

  37: [Extent1].[ModifiedDate] AS [ModifiedDate], 

  38: [Extent1].[ModifiedBy] AS [ModifiedBy]

  39: FROM [dbo].[ProductReturns] AS [Extent1]

  40: WHERE ([Extent1].[ProductReturnIDFull] LIKE @p__linq__0 ESCAPE N'~') OR ( EXISTS (SELECT 

  41:     1 AS [C1]

  42:     FROM [dbo].[LinesExpected] AS [Extent2]

  43:     WHERE ([Extent1].[ProductReturnID] = [Extent2].[ProductReturnID]) AND ([Extent2].[Serial] LIKE @p__linq__1 ESCAPE N'~')

  44: ))

 

Predicates are pretty sweet!