Linq with in() the where clause

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)

.Net Linq in Entity Framework 4 left outer join with isnull equivalent in where clause

To do a left outer join, please review blog http://smehrozalam.wordpress.com/2009/06/10/c-left-outer-joins-with-linq/ it explains it very well.

The problem I had was trying to get the left outer join table and use the value if it was there in the where clause.

Below is the sql I was going for..

   1: SELECT le.qty 

   2: FROM LinesExpected le

   3:     LEFT OUTER JOIN

   4:         LineReceipts lr

   5:             ON(le.LineExpectedID = lr.LineExpectedID)

   6: WHERE 

   7:     le.qty > isnull(lr.qty,0)

   8:     AND le.LineExpectedID != currentLineExpectedID

   9:     AND le.custid = "DM616"

  10:     AND le.itemid = "PI-29"

  11:     AND le.active = 1

And the linq that produces a left outer join and is able to perform the isnull equivalent looks like..

   1: var sql = (from le in LinesExpecteds

   2:  from lr in LineReceipts.Where(lr=>lr.LineExpectedID == le.LineExpectedID).DefaultIfEmpty()

   3:  where 

   4:     le.CustID == "DM616" && 

   5:     le.Active == true && 

   6:     le.ItemID == "pi-29" && 

   7:     le.LineExpectedID != 0 &&

   8:     le.Qty > (lr == null ? 0 : lr.Qty)

   9:  select le.Qty).ToList();

Notice line 8, that is where the magic occurs, the main table LinesExpected (le) is compared to either lr.Qty if it exist or Zero if it does not exist.

 

I got the idea from http://stackoverflow.com/questions/413084/equivalent-of-sql-isnull-in-linq but the answer was using the isnull in the select piece of the query so I thought I would blog about how to use similar idea in the where clause.

 

The TSQL generated (via LINQPad 4) is..

   1: SELECT 

   2: [Extent1].[Qty] AS [Qty]

   3: FROM  [dbo].[LinesExpected] AS [Extent1]

   4: LEFT OUTER JOIN [dbo].[LineReceipts] AS [Extent2] ON [Extent2].[LineExpectedID] = [Extent1].[LineExpectedID]

   5: WHERE (N'DM616' = [Extent1].[CustID]) AND (1 = [Extent1].[Active]) AND (N'pi-29' = [Extent1].[ItemID]) AND (0 <> [Extent1].[LineExpectedID]) AND ([Extent1].[Qty] > (CASE WHEN ([Extent2].[LineReceiptID] IS NULL) THEN cast(0 as decimal(18)) ELSE [Extent2].[Qty] END))

Dynamics Ax 4: Using Outlook object to resolve email address from Exchange User

Today I was using the outlook object model to read emails from outlook.  I was using

   1: fromEmail = mailItemClass.get_SenderEmailAddress();

I thought this would return something like john.smith@perfect-10.tv.  Instead it returned something like this

"/O=PERFECT10/OU=FIRST GROUP/CN=RECIPIENTS/CN=JOHNSM"

I needed to get the smtp email address, after a lot of searching I did not find anything that really worked within Ax.  So I wrote a function to do this in Ax..

   1: str ResolveEmail(str _emailAddress)

   2: {

   3:     Microsoft.Office.Interop.Outlook.Recipients recipients;

   4:     Microsoft.Office.Interop.Outlook.Recipient recipient, newRecipient;

   5:     Microsoft.Office.Interop.Outlook.AddressEntry addressEntry;

   6:     Microsoft.Office.Interop.Outlook.ExchangeUserClass exchangeUser;

   7:  

   8:     Microsoft.Office.Interop.Outlook._Application outAppl;

   9:     Microsoft.Office.Interop.Outlook.MAPIFolder mapiFolder;

  10:     Microsoft.Office.Interop.Outlook.NameSpaceClass Nspace;

  11:     Microsoft.Office.Interop.Outlook.ItemsClass itemClass;

  12:     Microsoft.Office.Interop.Outlook.OlDefaultFolders olFolderInbox

  13:         = CLRInterop::parseClrEnum('Microsoft.Office.Interop.Outlook.OlDefaultFolders','olFolderInbox');

  14:  

  15:  

  16:     int numOfEmails;

  17:     int numOfFolders;

  18:     str value;

  19:     int cnt;

  20:  

  21:     ;

  22:         try

  23:         {

  24:  

  25:             recipients = mailItemClass.get_Recipients();

  26:             cnt = recipients.get_Count();

  27:             recipients.Add(_emailAddress);

  28:             cnt = recipients.get_Count();

  29:             recipients.ResolveAll();

  30:             recipient = recipients.get_Item(cnt);

  31:             addressEntry = recipient.get_AddressEntry();

  32:             exchangeUser = addressEntry.GetExchangeUser();

  33:             if(exchangeUser)

  34:             {

  35:                 value = exchangeUser.get_PrimarySmtpAddress();

  36:             }

  37:             else

  38:             {

  39:                 value = _emailAddress;

  40:             }

  41:  

  42:         }

  43:         catch(Exception::CLRError)

  44:         {

  45:             error('cannot read email');

  46:             continue;

  47:         }

  48: //    }

  49:         return value;

  50: }

 

NOTE #1: The Dynamics Ax code to get the mailItemClass this uses is a global variable on the class and so is the FolderClass.  See this article to get the first mailItem..  http://dc313.4shared.com/doc/J3LRQLgs/preview.html

NOTE #2:  I found a article http://anoriginalidea.wordpress.com/2008/01/11/getting-the-smtp-email-address-of-an-exchange-sender-of-a-mailitem-from-outlook-in-vbnet-vsto/

which probably works but seemed like overkill to me, but luckily there was a comment in the article that had a simple .Net solution, which is what I used to translate into my Dynamics Ax method.  Here is the .Net solution I used based off the comment..Just create a standard VB App for windows forms and copy paste this code, add a reference to outlook dll.

   1:  

   2: Imports Microsoft.Office.Interop

   3:  

   4:  

   5:  

   6:  

   7: Public Class Form1

   8:  

   9:  

  10:  

  11:     Private Sub btnButton1_Click(sender As System.Object, e As System.EventArgs) Handles btnButton1.Click

  12:         MsgBox(fnGetSMTPAddress("/O=PERFECT10/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=LARRYB"))

  13:     End Sub

  14:  

  15:     Public Function fnGetSMTPAddress(ExchangeMailAddress As String) As String

  16:         Dim objOutlook As Outlook.Application

  17:         Dim objMailItem As Outlook.MailItem

  18:  

  19:         objOutlook = New Outlook.Application

  20:         objMailItem = objOutlook.CreateItem(0)

  21:         objMailItem.To = ExchangeMailAddress

  22:         objMailItem.Recipients.ResolveAll()

  23:         fnGetSMTPAddress = objMailItem.Recipients.Item(1).AddressEntry.GetExchangeUser.PrimarySmtpAddress

  24:         objMailItem = Nothing

  25:         objOutlook = Nothing

  26:  

  27:     End Function

  28:  

  29:  

  30: End Class

Display fields not showing up, but went ahead and tried it and it worked.

 

I am using Dynamics Ax 4.  I added the code below to a table

   1: display name ParentWebCategoryName()

   2: {

   3:     ptnWebCategories    ptnWebCategories;

   4:     name                name;

   5:     ;

   6:     ptnWebCategories = ptnWebCategories::find(this.webCategoryID);

   7:     name= ptnWebCategories.ParentCategoryName;

   8:     return name;

   9: }

In the past I thought that this would show up as a field when I added the table as a data source to a form.  I could not get it to show up for some reason but it did work, even in a grid..  Go figure..  I added a new field to a grid, changed the datasource to the table, changed the datamethod to the above method name, and it worked.