All posts tagged 'entity framework'

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)

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;
}

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

Above is the entity framework 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.