I’m ready for Netduino plus

More learning in my new role

My new role involves managing electronic, mechanical and software design and development for our group of companies. It has lead me to learn about writing software for “chips”.
We currently design and manufacture electronic hardware, these devices have standard analogue electronics and over the years they have increasingly incorporated microprocessors too. Commonly these are processors from the Microchip PIC, and some programmable logic controllers (PLCs) for faster video manipulation needs.

 

.NET Micro Framework.NET Micro Framework

For some time I have been aware of the .NET Microframework and have wanted to have a try of it, but without any good reason to, other emerging strands such as MVC, Entity Framework, WCF and more have taken priority. Thus now I find that the excuse is in place, dive in and see what it has to offer and importantly where it is appropriate in hardware development.

Microsoft .NET Micro Framework  is a cut down version of the full .NET framework ported to run on small processors. That said these “small” processors are more powerful than the ones I used to write my thesis on at university. Once programmed up, the chips can be embedded in larger hardware device designs. Our current hardware designs utilise Microchip PIC processors, programmed using C. The great leap we find is that the Micro Framework dumbs down the programming of microprocessors so that anyone who is experienced with C# can program a chip – really they can, it is that easy!

The performance suffers resulting in less grunt from the .NET processors, and yes there is much less choice on the .NET platform of microprocessors than the vast choices found in the Microchip PIC range. Also expect the PIC developer in the dev lab will turn their nose up at you, but we don’t care, we are used to being shunned as .NET developers. What it can happen, is skilled programmers from the enterprise development team may fulfil rapid prototyping requirements in the hardware domain. Indeed if the solution is a one off it may also be appropriate to produce the production hardware using this platform.

 

Good for rapid prototyping and hobbyists

I yet have to be convinced that .NET framework processors are appropriate for mass produced goods as they are relatively expensive compared to PIC alternatives. I can see that for niche products with small runs, the advantage in speed to market against the price of the processor may be something that is appealing for some projects. With about a £30 entry point for development boards it is great for the hobbyist to get started without the hurdle of great expense.

 

Arduino

Anyone who has been to a Maker Faire will know the popularity of the Arduino boards for the hobby market. These small processor boards come in a standard factor that allows pre-made peripheral hardware devices, like displays, GPS receivers, infrared sensor to be plugged in, reducing skills and time to produce something fun. As the difficult task of electronic design is packaged up for you, more time can be made of the fun bits of the designing of software to run on it.

Netduino & Netduino Plus

Secret labs have produced a Arduino compatible board, a board that works with most of the plug in peripherals that the well established Arduino community enjoy. Named the Netduino, the board uses a processor that is programmed using the .NET Micro Framework. There are other similar Arduino compatible boards around, notably the FEZ Panda and Domino range that are slightly more powerful, but at a higher price. Perhaps not as attractive as a starting point. Remember we are in the hardware domain now, you may end up blowing these things up, leaving you with no option but buying a new one, reboot will bring things back to how they were. The development environment and process is very familiar. Start up your Visual Studio, download the SDK for .NET Micro Framework and the SDK for the board, reference them in code, then start programming. Use Visual Studio to deploy to the hardware board and enjoy your LED flashing or robot move!

A buzz is around at the moment (Nov. 2010), a new version of the Netduino is emerging from Beta, this adds much more to the basic board, such as TCP/IP natively on board & MicroSD slot, all without having to buy external peripherals. Considering most potential projects I think of for work and home require to be connected to the internet or corporate network, this is a great move! I am expecting delivery of mine before Christmas and I’ll bog a bit more when I have it.

 

and beyond

There is a port of the .NET Micro Framework for the Blackfin processor family, AxiDotNet so if you need to do some serious number crunching, for example video processing, go in that direction.

It is exciting that I get a chance to work on another .NET framework platform and will it will be interesting to see what I can do with it. I might even make a presentation for our local developer user group if I feel confident enough!

You might find the Chris Walker’s conversation with Scott Hanselman a good place to start if you have a spare twenty minutes to spare Hanselminutes - Deeper into the Netduino with Chris Walker from Secret Labs.

 

References:

.NET Micro Framework home http://www.microsoft.com/netmf/default.mspx

Netduino web site http://www.netduino.com/

Arduino website http://www.arduino.cc/

Microchip website http://www.microchip.com/

Maker Faire website http://makerfaire.com

EZ boards

Blackfin processor family

AxiDotNet Blackfin .NET Microframework

Extending .NET Micro Framework with an MPEG-4 Video Decoder

 

Dynamics GP – Copy address button using VBA & Modifier

Introduction - why add a copy button?

Dynamics GP Sales Order Processing (SOP) allows orders to be placed against debtors (customers). For each customer you can have any number of addresses stored, this address book can then be used by sales staff when an order is placed to avoid rekeying the information.

We needed to start storing an address per contact as the contact’s name is captured within the address block. Some delivery sites for national organisations are huge, many thousands of people are fed from one central goods in delivery point. This means that each contact email address is essentially the same bar for the contact name.

Requirement

To speed up order entry and address capture and accuracy, a requirement was identified to allow SOP users to copy a master address for these larger site. This would then merely require sales to add a contact name to the account master template address they copy, rather than manually copying and pasting the address each time.

Add a copy button & combo box to address windows

Using Microsoft Dynamics Modifier, select the address entry screens for GP. You might choose

DebtorAddressMaintenance & SalesShipToAddressEntry, for example. If you use 3rd party dictionaries, watch for alternatives to these in those dictionaries.

Drop a new button onto each form, rename the button btnCopy for this example.
Drop a drop down combobox to the form, name it ddlAddress

Modified GP Dynamics Address Form

When the form is loaded with data, the combobox will be filled with the list of address ID’s available for this customer. By default the default address ID will select itself, and the selected address ID will be used as the source for the copy address function. The copy address functionality will be invoked by the pressing of the btnCopy.

Save the modified form, if you do not currently have permissions set up for the modified form, please ensure your sers are pointed by security at the new form.

VBA behind the form

Now it must be remembered to add the window and fields to VBA that were created. Those are the button and the combobox, also needed is to add the customer number field and each of the address fields that are to be copied to VBA.

For this article I will assume we are working with the DebtorAddressMaintenance window. For this window when a new debtor number is entered, then the combobox that was added must be populated with the available AddressID’s for that customer. The combobox is also required to default to the customer address ID selected in the main customer maintenance card as the default ship address.

SQL

We must query the Dynamics GP database for all the address IDs for this customer stored in the “address book”.

ALTER PROCEDURE [yourschema].[SOP_GetCustomerAddressCodes]
   @CUSTNMBR  varchar(15)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT     RM00102.ADRSCODE, ISNULL(RM00101.PRSTADCD,'') 
        as SHIPPINGADRSCODE
    FROM RM00102
    LEFT JOIN 
         RM00101 
    ON RM00102.CUSTNMBR=RM00101.CUSTNMBR
    WHERE  
      (RM00102.CUSTNMBR = @CUSTNMBR) 
  
END
 

And to get a specific address

 

ALTER PROCEDURE [yourschema].[SOP_GetCustomerAddress]
   @CUSTNMBR  varchar(15),
   @ADRSCODE  varchar(15)    
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CUSTNMBR, ADRSCODE, SLPRSNID, UPSZONE, SHIPMTHD,
           TAXSCHID, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY, CITY, STATE, 
           ZIP, PHONE1, PHONE2, PHONE3, FAX, MODIFDT, CREATDDT, GPSFOINTEGRATIONID,
           INTEGRATIONSOURCE, INTEGRATIONID, CCode, DECLID, 
           LOCNCODE, SALSTERR, USERDEF1, USERDEF2, DEX_ROW_ID
    FROM   RM00102
    WHERE  (CUSTNMBR = @CUSTNMBR) 
       AND (ADRSCODE = @ADRSCODE)
 
END

Address Class

A class called clsSOPAddress is made in the class modules section of your VBA project as follows;

Option Explicit
Private m_CustomerNumber As String
Private m_AddressID As String
Private m_Contact As String
Private m_Address1 As String
Private m_Address2 As String
Private m_Address3 As String
Private m_City As String
Private m_County As String
Private m_PostCode As String
Private m_CountryCode As String
Private m_Country As String
Private m_Phone1 As String
Private m_Phone2 As String
Private m_Phone3 As String
Private m_Fax As String
Private m_CarrierZone As String
Private m_ShippingMethod As String
Private m_TaxScheduleID As String
Private m_SiteID As String
Private m_SalesPersonID As String
Private m_TerritoryID As String
Private m_UserDefined1 As String
Private m_UserDefined2 As String



Public Property Let CustomerNumber(CustNumber As String)
m_CustomerNumber = CustNumber
End Property
Public Property Get CustomerNumber() As String
CustomerNumber = m_CustomerNumber
End Property

Public Property Let AddressID(Adrid As String)
m_AddressID = Adrid
End Property
Public Property Get AddressID() As String
AddressID = m_AddressID
End Property

Public Property Get Contact() As String
Contact = m_Contact
End Property
Public Property Get Address1() As String
Address1 = m_Address1
End Property
Public Property Get Address2() As String
Address2 = m_Address2
End Property
Public Property Get Address3() As String
Address3 = m_Address3
End Property
Public Property Get City() As String
City = m_City
End Property
Public Property Get County() As String
County = m_County
End Property
Public Property Get Postcode() As String
Postcode = m_PostCode
End Property
Public Property Get CountryCode() As String
CountryCode = m_CountryCode
End Property
Public Property Get Country() As String
Country = m_Country
End Property
Public Property Get Phone1() As String
Phone1 = m_Phone1
End Property
Public Property Get Phone2() As String
Phone2 = m_Phone2
End Property
Public Property Get Phone3() As String
Phone3 = m_Phone3
End Property
Public Property Get Fax() As String
Fax = m_Fax
End Property
Public Property Get CarrierZone() As String
CarrierZone = m_CarrierZone
End Property
Public Property Get ShippingMethod() As String
ShippingMethod = m_ShippingMethod
End Property
Public Property Get TaxScheduleID() As String
TaxScheduleID = m_TaxScheduleID
End Property
Public Property Get SiteID() As String
SiteID = m_SiteID
End Property
Public Property Get SalespersonID() As String
SalespersonID = m_SalesPersonID
End Property
Public Property Get TerritoryID() As String
TerritoryID = m_TerritoryID
End Property
Public Property Get UserDefined1() As String
UserDefined1 = m_UserDefined1
End Property
Public Property Get UserDefined2() As String
UserDefined2 = m_UserDefined2
End Property

Public Sub LoadAddress()
On Error GoTo ErrorHandler
Dim SQLRec As New ADODB.Recordset 'holds recordset SQL

Dim Cmd1 As ADODB.Command
Set Cmd1 = New ADODB.Command

Dim SQLCon As ADODB.Connection
Set SQLCon = New ADODB.Connection

If SQLCon.State <> adStateOpen Then SQLCon.Open Microsoft_Dynamics_GP.GetConnectionStr
Cmd1.ActiveConnection = SQLCon
Cmd1.CommandText = "SOP_GetCustomerAddress"
Cmd1.CommandType = adCmdStoredProc
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter

Set prm1 = Cmd1.CreateParameter("@CUSTNMBR", adVarChar, adParamInput, 15, Trim(Me.CustomerNumber))
Set prm2 = Cmd1.CreateParameter("@SOPNUMBE", adVarChar, adParamInput, 15, Trim(Me.AddressID))
With Cmd1
.Parameters.Append prm1
.Parameters.Append prm2
End With

SQLRec.Open Cmd1.Execute
If Not SQLRec.BOF And Not SQLRec.EOF Then
m_Contact = Trim(SQLRec.Fields("CNTCPRSN").Value)
m_Address1 = Trim(SQLRec.Fields("ADDRESS1").Value)
m_Address2 = Trim(SQLRec.Fields("ADDRESS2").Value)
m_Address3 = Trim(SQLRec.Fields("ADDRESS3").Value)
m_City = Trim(SQLRec.Fields("CITY").Value)
m_County = Trim(SQLRec.Fields("STATE").Value)
m_PostCode = Trim(SQLRec.Fields("ZIP").Value)
m_Phone1 = Trim(SQLRec.Fields("PHONE1").Value)
m_Phone2 = Trim(SQLRec.Fields("PHONE2").Value)
m_Phone3 = Trim(SQLRec.Fields("PHONE3").Value)
m_Fax = Trim(SQLRec.Fields("FAX").Value)
m_CountryCode = Trim(SQLRec.Fields("CCODE").Value)
m_Country = Trim(SQLRec.Fields("COUNTRY").Value)
m_CarrierZone = Trim(SQLRec.Fields("UPSZONE").Value)
m_ShippingMethod = Trim(SQLRec.Fields("SHIPMTHD").Value)
m_TaxScheduleID = Trim(SQLRec.Fields("TAXSCHID").Value)
m_SiteID = Trim(SQLRec.Fields("LOCNCODE").Value)
m_SalesPersonID = Trim(SQLRec.Fields("SLPRSNID").Value)
m_TerritoryID = Trim(SQLRec.Fields("SALSTERR").Value)
m_UserDefined1 = Trim(SQLRec.Fields("USERDEF1").Value)
m_UserDefined2 = Trim(SQLRec.Fields("USERDEF2").Value)


Else
'Address not found...
End If


'Close database connection and clean up
If CBool(SQLRec.State And adStateOpen) = True Then SQLRec.Close
Set SQLRec = Nothing

If CBool(SQLCon.State And adStateOpen) = True Then SQLCon.Close
Set SQLCon = Nothing

Set prm1 = Nothing
Set prm2 = Nothing
Set Cmd1 = Nothing

'Break before error hander
Exit Sub
ErrorHandler:
MsgBox "VBA Error trying to get address" + vbCrLf _
& vbCrLf & Err.Description, vbCritical + vbOKOnly, "Error in get address"
End Sub

Public Function GetAvailableAddressCodes() As String()
On Error GoTo ErrorHandler
Dim Results() As String
ReDim Results(0 To 0)
Dim SQLRec As New ADODB.Recordset 'holds recordset SQL

Dim Cmd1 As ADODB.Command
Set Cmd1 = New ADODB.Command

Dim SQLCon As ADODB.Connection
Set SQLCon = New ADODB.Connection

If SQLCon.State <> adStateOpen Then SQLCon.Open Microsoft_Dynamics_GP.GetConnectionStr
Cmd1.ActiveConnection = SQLCon
Cmd1.CommandText = "SOP_GetCustomerAddressCodes"
Cmd1.CommandType = adCmdStoredProc
Dim prm1 As New ADODB.Parameter

Set prm1 = Cmd1.CreateParameter("@CUSTNMBR", adVarChar, adParamInput, 15, Trim(Me.CustomerNumber))
With Cmd1
.Parameters.Append prm1
End With

SQLRec.Open Cmd1.Execute

Dim RecordCount As Integer
RecordCount = 0

Do While Not SQLRec.EOF

ReDim Preserve Results(RecordCount)
If SQLRec.Fields("ADRSCODE").Value = SQLRec.Fields("SHIPPINGADRSCODE").Value Then
Results(RecordCount) = "%" & SQLRec.Fields("ADRSCODE").Value
Else
Results(RecordCount) = SQLRec.Fields("ADRSCODE").Value
End If

RecordCount = RecordCount + 1
SQLRec.MoveNext
Loop

'Close database connection and clean up
If CBool(SQLRec.State And adStateOpen) = True Then SQLRec.Close
Set SQLRec = Nothing

If CBool(SQLCon.State And adStateOpen) = True Then SQLCon.Close
Set SQLCon = Nothing

Set prm1 = Nothing
Set Cmd1 = Nothing
GetAvailableAddressCodes = Results
'Break before error hander
Exit Function
ErrorHandler:
MsgBox "VBA Error trying to get address" + vbCrLf _
& vbCrLf & Err.Description, vbCritical + vbOKOnly, "Error in get address"
End Function

This class will handle fetching of the addresses. The default address is marked by a % that allows the combo box to by default select the address that contains that symbol and use it as default selection.

Now introduce a new module, utils, in the VBA project, into it add the following function for getting the list of addresses;

Public Function GetAddresses(DebtorID As String) As String()
On Error GoTo ErrorHandler

Dim osopAddress As New clsSOPAddress
osopAddress.CustomerNumber = DebtorID
GetAddresses = osopAddress.GetAvailableAddressCodes
Exit Function
ErrorHandler:
MsgBox "VBA Error trying to get available address ids." + vbCrLf _
+ " " + vbCrLf _
& vbCrLf & Err.Description, vbCritical + vbOKOnly, _
"Error in GetAddress"
End Function

We can now call this function from our address form in order to fill our combobox, We do this by using a unsupported (but fully working) method of calling dex code from VBA. In this case we are running this form from a 3rd party credit control form, hence the referenced dictionary.

Private Sub DebtorID_Changed()
On Error GoTo ErrorHandler
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim CompilerCommand As String

Set CompilerApp = CreateObject("Dynamics.Application")
CompilerCommand = ""
CompilerCommand = CompilerCommand & _
"clear '(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address;"_
& vbCrLf

ReDim oAddressList(0 To 0) 'initialise to zero for ubound checking
If Not DebtorID.Empty Then
oAddressList = utils.GetAddresses(DebtorID.Value)
Dim currentIndex As Integer
Dim defaultIndex As Integer
defaultIndex = -1
For currentIndex = LBound(oAddressList) To UBound(oAddressList)
If Left(oAddressList(currentIndex), 1) = "%" Then
CompilerCommand = CompilerCommand & "add item "" " & _
Mid(oAddressList(currentIndex), 2) & """, " & CStr(currentIndex) & vbCrLf
defaultIndex = currentIndex
Else
CompilerCommand = CompilerCommand & "add item "" " & oAddressList(currentIndex) _
& """, " & CStr(currentIndex) & vbCrLf
End If
CompilerCommand = CompilerCommand &_
" to '(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address;" _
& vbCrLf
Next currentIndex
End If
If defaultIndex = -1 Then defaultIndex = 0
CompilerCommand = CompilerCommand & _
"'(L) ddlAddresses' of window RM_Customer_Address of form RM_Customer_Address = "_
& Str(defaultIndex + 1) & ";" & vbCrLf

' Execute SanScript
CompilerApp.CurrentProductID = 2422 ' Nolans Credit Control
CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
If CompilerError <> 0 Then
MsgBox CompilerMessage
End If
'exit sub before the error handling kicks in
Exit Sub
ErrorHandler:
MsgBox "An Error occured on debtorid change." + vbCrLf + "" & vbCrLf & Err.Description,_
vbCritical + vbOKOnly, "Error in DebtorID.Changed"
End Sub

Finally the copy itself

When the uses presses btncopy the address associated with the addresscode shown by the combobox is got and it is used to populate the form fields.

Private oAddressList() As String ' holds addresslist for lookup

Private Sub Copy_BeforeUserChanged(KeepFocus As Boolean, _
CancelLogic As Boolean)
On Error GoTo ErrorHandler
If Not DebtorID.Empty Then
Dim oAddress As clsSOPAddress
If Left(oAddressList(ddlAddresses.Value - 1), 1) = "%" Then
Set oAddress = utils.GetCustomerAddress(Me.DebtorID, _
Mid(oAddressList(ddlAddresses.Value - 1), 2))
Else
Set oAddress = utils.GetCustomerAddress(Me.DebtorID, _
oAddressList(ddlAddresses.Value - 1))
End If

Contact.Value = oAddress.Contact
Address.Value = oAddress.Address1
Address1.Value = oAddress.Address2
Address2.Value = oAddress.Address3
City.Value = oAddress.City
County.Value = oAddress.County
Postcode.Value = oAddress.Postcode
Country.Value = oAddress.Country
CountryCode.Value = oAddress.CountryCode
Phone1.Value = oAddress.Phone1
Phone2.Value = oAddress.Phone2
Phone3.Value = oAddress.Phone3
Fax.Value = oAddress.Fax
CarrierZone.Value = oAddress.CarrierZone
ShippingMethod.Value = oAddress.ShippingMethod
TaxScheduleID.Value = oAddress.TaxScheduleID
SiteID.Value = oAddress.SiteID
SalespersonID.Value = oAddress.SalespersonID
TerritoryID.Value = oAddress.TerritoryID
UserDefined1.Value = oAddress.UserDefined1
End If
'exit sub before the error handling kicks in
Exit Sub

Entity framework learning curve

I have just started using the Entity Framework (EF) to create some quick winforms for our Dynamics GP modifications. However I’ve been bogged down by performance issues. Writing the data layer in datasets brings my performance back to what I expect again.
It is possible, even normal to use stored procedures to gain control again, but for these forms it seemed over the top of the use they get, and I just wanted a quick and dirty implantation for a quick win. The performance arguments for stored procedures are getting thinner and in our environment they create a maintenance burden. Don’t get me wrong, the use of stored procedures and views has many times got me out a hole in avoiding recompiling applications, instead just a tweaking of the procedure driving the application.

Update from investigations:
Entity Framework 4 should improve on generated SQL. The issue I experienced here is the unicode one listed on the ADO.NET team blog,  Provide mechanism for efficient queries on non-Unicode columns, this is issue 5 in the list.

In .NET 3.5, whenever a constant or a parameter was used in LINQ to Entities query, we treated it as being Unicode. As a result, when comparing a constant to a property stored in a non-unicode column on SQL Server, if there was an index on that column, it was not being used.

To address the issue, we now generate non-unicode constants and parameters when these are used in LINQ to Entities queries in comparisons with non-unicode columns.

Original problem EF generated SQL has wrong types

Table Defined

The table I am working with is defined like so;

 
/****** Object:  Table [dbo].[CA_PICKLSTMAST]    Script Date: 11/09/2010 10:16:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CA_PICKLSTMAST](
    [SOPTYPE] [smallint] NOT NULL,
    [SOPNUMBE] [char](21) NOT NULL,
    [LASTAMENDED] [datetime] NOT NULL,
    [VERSION] [smallint] NOT NULL,
    [VOID] [bit] NOT NULL,
    [ACTIVE] [bit] NOT NULL,
    [PRINTED] [bit] NOT NULL,
    [TIMEPRINTED] [datetime] NULL,
    [ORDERTIMESTAMP] [datetime] NOT NULL,
    [CUSTNAME] [char](65) NULL,
    [PICKVALUE] [numeric](19, 5) NULL,
    [SHIPMTHD] [char](15) NULL,
    [PICKCODE] [char](3) NULL,
    [DOCID] [char](15) NULL,
    [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_CA_PICKLSTMAST] PRIMARY KEY NONCLUSTERED 
(
    [DEX_ROW_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IX_CA_PICKLSTMAST] ON [dbo].[CA_PICKLSTMAST] 
(
    [SOPTYPE] ASC,
    [SOPNUMBE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_CA_PICKLSTMAST_1] ON [dbo].[CA_PICKLSTMAST] 
(
    [SOPTYPE] ASC,
    [SOPNUMBE] ASC,
    [VERSION] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Random Code to access this pick list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CA_PICKLSTMAST', @level2type=N'COLUMN',@level2name=N'PICKCODE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'DOCID of order' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CA_PICKLSTMAST', @level2type=N'COLUMN',@level2name=N'DOCID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Holds the master records for pick lists generated by vb.net canford application.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CA_PICKLSTMAST'
GO
Linq

I want to select all the records that match a certain SOPTYPE and SOPNUMBE.

Dim QueryCA_PICKLSTMAST As ObjectQuery(Of CA_PICKLSTMAST) _
= CType((From results In oSalesOrderProcessingEnity.CA_PICKLSTMAST _
Where results.SOPNUMBE = Me.SopNumber And results.SOPTYPE = Me.SOPTYPE), _
ObjectQuery(Of CA_PICKLSTMAST))
CAPICKLSTMASTBindingSource.DataSource = QueryCA_PICKLSTMAST.Execute(MergeOption.NoTracking)

Where the params are defined as;
Private m_SopNumber As String
Public Property SopNumber() As String
Get
Return m_SopNumber
End Get
Set(ByVal value As String)
If m_SopNumber <> value Then
m_SopNumber = value
RefreshForm()
End If

End Set
End Property

Private m_SOPTYPE As Short = 2
Public Property SOPTYPE() As Short
Get
Return m_SOPTYPE
End Get
Set(ByVal value As Short)
m_SOPTYPE = value
End Set
End Property
 
Resulting TSQL executed against SQL server
Now using datasets I get TSQL like this;
exec sp_executesql 
N'SELECT SOPTYPE,
 SOPNUMBE, LASTAMENDED, VERSION, VOID, ACTIVE, 
 PRINTED, TIMEPRINTED, ORDERTIMESTAMP, CUSTNAME, 
 PICKVALUE, SHIPMTHD, PICKCODE, 
 DOCID, DEX_ROW_ID
FROM CA_PICKLSTMAST
WHERE 
SOPTYPE=@SOPTYPE AND SOPNUMBE=@SOPNUMBE',
N'@SOPTYPE smallint,@SOPNUMBE char(21)',
@SOPTYPE=2,
@SOPNUMBE='W36077               '
 

 

Just as I would get if crafted by hand, however EF creates the following;

exec sp_executesql N'SELECT 
[Extent1].[SOPTYPE] AS [SOPTYPE], 
[Extent1].[SOPNUMBE] AS [SOPNUMBE], 
[Extent1].[LASTAMENDED] AS [LASTAMENDED], 
[Extent1].[VERSION] AS [VERSION], 
[Extent1].[VOID] AS [VOID], 
[Extent1].[ACTIVE] AS [ACTIVE], 
[Extent1].[PRINTED] AS [PRINTED], 
[Extent1].[TIMEPRINTED] AS [TIMEPRINTED], 
[Extent1].[ORDERTIMESTAMP] AS [ORDERTIMESTAMP], 
[Extent1].[CUSTNAME] AS [CUSTNAME], 
[Extent1].[PICKVALUE] AS [PICKVALUE], 
[Extent1].[SHIPMTHD] AS [SHIPMTHD], 
[Extent1].[PICKCODE] AS [PICKCODE], 
[Extent1].[DOCID] AS [DOCID], 
[Extent1].[DEX_ROW_ID] AS [DEX_ROW_ID]
FROM [dbo].[CA_PICKLSTMAST] AS [Extent1]
WHERE 
([Extent1].[SOPNUMBE] = @p__linq__101) 
AND 
([Extent1].[SOPTYPE] = @p__linq__102)',
N'@p__linq__101 nvarchar(6),
@p__linq__102 smallint',
@p__linq__101=N'W36077',
@p__linq__102=2

 

Notice all the type conversion going on, this is what I guess is causing my issue, nvarchar(6), for example...

This then kills my query performance (I have many more of these running on the Winform, so the form becomes very sluggish.

Execution Plans compared

EF Query plan

Above is the entity framework query plan.

Dataset Query plan

Above is the dataset query plan, look at the execution times and the way the load have been moved from an index to costly joins.

Solution

 

Entity Framework 4 should improve on generated SQL. The issue I experienced here is the unicode one listed on the ADO.NET team blog,  Provide mechanism for efficient queries on non-Unicode columns, this is issue 5 in the list.

In .NET 3.5, whenever a constant or a parameter was used in LINQ to Entities query, we treated it as being Unicode. As a result, when comparing a constant to a property stored in a non-unicode column on SQL Server, if there was an index on that column, it was not being used.

To address the issue, we now generate non-unicode constants and parameters when these are used in LINQ to Entities queries in comparisons with non-unicode columns.

Off I go to try it out with EF4 CTP…