Upgrading GP Addin Mods to use EF6

After adding the configuration sections to Dynamics.exe.config

<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>

and adding the connnection strings section, the following error  occurs…

Top-level Exception
Type: System.InvalidOperationException
Message: The Entity Framework provider type 'System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SqlClient' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.
Source: EntityFramework
Stack Trace: at System.Data.Entity.Infrastructure.DependencyResolution.ProviderServicesFactory.GetInstance(String providerTypeName, String providerInvariantName)
at System.Data.Entity.Internal.AppConfig.<;.ctor>b__2(ProviderElement e)
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at System.Data.Entity.Internal.AppConfig.<;.ctor>b__1()
at System.Lazy`1.CreateValue()
at System.Lazy`1.LazyInitValue()
at System.Lazy`1.get_Value()
at System.Data.Entity.Internal.AppConfig.get_DbProviderServices()
at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.RegisterDbProviderServices()
at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetServiceFactory(Type type, String name)
at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.<;>c__DisplayClass1.<GetService>b__0(Tuple`2 t)
at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<;>c__DisplayClass3.<GetService>b__0(IDbDependencyResolver r)
at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.CompositeResolver`2.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetService[T](IDbDependencyResolver resolver, Object key)
at System.Data.Entity.Core.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString)
at System.Data.Entity.Core.EntityClient.EntityConnection..ctor(String connectionString)
at canford.SalesOrderProcessingEntities.GetSqlConnection(String ConnectionString)

Seems that as I have all the data access defined in its own class lib, there is no code causing a reference to force the correct EntityFramework.SqlServer.dll to be pushed to the build output.

So this hack did the job for now and I’ve posted this here to come back to another time.

Stack overflow post: EntityFramework.SqlServer.dll not is getting added

private void FixEfProviderServicesProblem()
{
// The Entity Framework provider type 'System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer'
// for the 'System.Data.SqlClient' ADO.NET provider could not be loaded.
// Make sure the provider assembly is available to the running application.
// See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.
var instance = System.Data.Entity.SqlServer.SqlProviderServices.Instance;
}

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…