Dynamics Ax and .Net Dev Stories: March 2012

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!

I recently needed to do a select with a in() clause, so T sql needed to be

   1: select * from ProductReturns pr inner join LinesExpected le on(pr.ProductReturnID=le.ProductReturnID)   2: where pr.ProductReturnID in(4, 3, 9, 10, 27)

To accomplish this I used an array and did a “contains” within it. In C# looks like following

   1: int[] numbers = new int[5] {4, 3, 9, 10, 27};   2:     3:     4: var query = from i in ProductReturns   5:             from c in LinesExpecteds   6:             where i.ProductReturnID == c.ProductReturnID   7:             && numbers.Contains(i.ProductReturnID)   8:             select i;   9:    10: var items = query.ToList();

Resulting SQL is

   1: SELECT    2: [Extent1].[ProductReturnID] AS [ProductReturnID],    3: [Extent1].[ProductReturnIDFull] AS [ProductReturnIDFull],    4: [Extent1].[ReturnStatusID] AS [ReturnStatusID],    5: [Extent1].[ReturnSystemStatusID] AS [ReturnSystemStatusID],    6: [Extent1].[CustID] AS [CustID],    7: [Extent1].[ContactName] AS [ContactName],    8: [Extent1].[ContactPhone] AS [ContactPhone],    9: [Extent1].[ContactEmail] AS [ContactEmail],   10: [Extent1].[ShipFromAddress1] AS [ShipFromAddress1],   11: [Extent1].[ShipFromAddress2] AS [ShipFromAddress2],   12: [Extent1].[ShipFromCity] AS [ShipFromCity],   13: [Extent1].[ShipFromState] AS [ShipFromState],   14: [Extent1].[ShipFromZipCode] AS [ShipFromZipCode],   15: [Extent1].[ReturnWarehouse] AS [ReturnWarehouse],   16: [Extent1].[PrepaidLabelCount] AS [PrepaidLabelCount],   17: [Extent1].[PrepaidLabelPDF] AS [PrepaidLabelPDF],   18: [Extent1].[PalletCountCarrier] AS [PalletCountCarrier],   19: [Extent1].[PalletCountTotalReturn] AS [PalletCountTotalReturn],   20: [Extent1].[PickUpDate] AS [PickUpDate],   21: [Extent1].[PickUpTime] AS [PickUpTime],   22: [Extent1].[IsLiftGateRequired] AS [IsLiftGateRequired],   23: [Extent1].[IsShrinkWrapRequired] AS [IsShrinkWrapRequired],   24: [Extent1].[IsPalletJackRequired] AS [IsPalletJackRequired],   25: [Extent1].[IsRestockingFee] AS [IsRestockingFee],   26: [Extent1].[RestockingFeeWaivedComment] AS [RestockingFeeWaivedComment],   27: [Extent1].[TotalPrepaidWeight] AS [TotalPrepaidWeight],   28: [Extent1].[TotalWeight] AS [TotalWeight],   29: [Extent1].[UpdateCounter] AS [UpdateCounter],   30: [Extent1].[Active] AS [Active],   31: [Extent1].[CreateDate] AS [CreateDate],   32: [Extent1].[CreatedBy] AS [CreatedBy],   33: [Extent1].[ModifiedDate] AS [ModifiedDate],   34: [Extent1].[ModifiedBy] AS [ModifiedBy]  35: FROM  [dbo].[ProductReturns] AS [Extent1]  36: INNER JOIN [dbo].[LinesExpected] AS [Extent2] ON [Extent1].[ProductReturnID] = [Extent2].[ProductReturnID]  37: WHERE [Extent1].[ProductReturnID] IN (4,3,9,10,27)

Dynamics Ax and .Net Dev Stories

Thursday, March 1, 2012

Simple example for Linq predicates (multiple tables)

Linq with in() the where clause