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…

Crosstab Microsoft Dynamics GP price tables

Rows to columns for price breaks

No doubt your sales team want to go on the road with a human friendly version of your prices for the customers to read. It is possible to do this with a SQL table function to extract the prices from GP with price breaks. The following example assumes you know how many price breaks you have in your price lists and will result in output something like the following screen shot. These results may be squirted into excel with more columns as described by your business requirements.
Results of join with IV00101 showing description pulled in

Two key SQL server functions that many people I find are not familiar with but are vital for this kind of data manipulation are; “ROW_NUMBER()” and “PARTITION BY” , one way to learn is to dive in with an example.

GP Price Table

Natively the prices are held in the table IV00108 of your company database.

ITEMNMBR CURNCYID PRCLEVEL UOFM TOQTY FROMQTY UOMPRICE
WIRE100 Z-US$ RETAIL Foot 100 0.01 0.35
WIRE100 Z-US$ RETAIL Foot 999999999999.99 100.01 0.29
WIRE100 Z-US$ RETAIL Spool 999999999999.99 0.01 190
WIRE100 Z-US$ RETAIL Yard 999999999999.99 0.01 0.65
WIRE100 Z-US$ EXTPRCLVL Foot 999999999999.99 0.01 0
WIRE100 Z-US$ EXTPRCLVL Yard 999999999999.99 0.01 0
WIRE100 Z-US$ EXTPRCLVL Spool 999999999999.99 0.01 0

There is a row per “price point”. Each row contains, the item sku, Currency of the price list, price list name, unit of measure, quantity break range and price.

This is unreadable to humans once you get, say 15,000 products, five currencies and ten or so price levels. From experience, one company this solution is used with has 1,623,586 rows in the price table IV00108.

Table Partitioning

Firstly the rows are grouped together by the common factor each output row should be sharing. Each row in this example should have the same Item, Currency, Price Level and unit of measure. A row number is added for each successive row within this grouping;

SELECT 
ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, TOQTY, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID, UOFM
ORDER BY toqty ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) WHERE itemnmbr='WIRE100'

The above TSQL partitions the returned rows from IV00108 by ITEMNMBR,PRCLEVEL, CURNCYID, UOFM, for each row in the group a row number is generated by ROW_NUMBER() see the following output example. For this example, there are two quanity break columns for the prices of the “foot” unit of measure.
These are breaks of; 0.01+ and 100+, resulting in row numbers one and two for this unit of measure.

ITEMNMBR CURNCYID PRCLEVEL UOMPRICE FROMQTY UOFM TOQTY RowNumber
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Foot

999999999999.99 1
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Spool

999999999999.99 1
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Yard

999999999999.99 1
WIRE100 Z-US$ RETAIL 0.35000 0.01000

Foot

100 1
WIRE100 Z-US$ RETAIL 0.29000 100.01000

Foot

999999999999.99 2
WIRE100 Z-US$ RETAIL 190.00000 0.01000

Spool

999999999999.99 1
WIRE100 Z-US$ RETAIL 0.65000 0.01000

Yard

999999999999.99 1

 

Now that we have the RowNumber, this can act as the anchor field to crosstab the data with. It makes sense to wrap this query in a common table expression (CTE) to clean it up. The output from the below should be identical to that above.

WITH PriceTable 
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr='WIRE100'
)
SELECT * FROM PriceTable

Crosstabbing the Common Table Expression

Now building on the select statement from the CTE, it is crosstabbed by using CASE statements as shown below. All that has changed between these two scripts is the select out of the CTE. The select is also add “+” to the price from column results as well as some NULL handling to make the presentation cleaner for Excel should it end up there. This is optional, it might be more appropriate for other uses to keep the results as numeric values and do that kind of processing in the reporting tool.

WITH PriceTable
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr='WIRE100'
)

select itemnmbr,
isnull(max(case when PriceTable.[RowNumber] = 1 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break1
, max(case when PriceTable.[RowNumber] = 1 then
uomprice end) as Price1
, isnull(max(case when PriceTable.[RowNumber] = 2 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break2
, max(case when PriceTable.[RowNumber] = 2 then
uomprice end) as Price2
,isnull( max(case when PriceTable.[RowNumber] = 3 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break3
, max(case when PriceTable.[RowNumber] = 3 then
uomprice end) as Price3
,isnull( max(case when PriceTable.[RowNumber] = 4 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break4
, max(case when PriceTable.[RowNumber] = 4 then
uomprice end) as Price4
, isnull(max(case when PriceTable.[RowNumber] = 5 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break5
, max(case when PriceTable.[RowNumber] = 5 then
uomprice end) as Price5
, isnull(max(case when PriceTable.[RowNumber] = 6 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break6
, max(case when PriceTable.[RowNumber] = 6 then
uomprice end) as Price6
from PriceTable
group by itemnmbr, curncyid, prclevel, UOFM;

 
 
The above TSQL generates the following table, where the rows have been transformed into columns by TSQL, just as required.
itemnmbr Break1   Price1 Break2 Price2 Break3 Price3 Break4 Price4 Break5 Price5 Break6 Price6
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.35000 100+ 0.29000   NULL   NULL   NULL   NULL
WIRE100 0+ 190.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.65000   NULL   NULL   NULL   NULL   NULL

Table valued function


Great there we have it, price table partitioned and crosstabbed. Lets not stop there as this is much more useful as a table valued function. This is achieved by wrapping the above SQL as shown below. Here we have decided that the calling script should provide the currency, pricelist, item pattern and unit of measure to export. Your application may differ and not require the expensive type conversions.

CREATE function [dbo].[Extract_PricesCrosstabTable] (
@CURNCYID varchar(15),
@PRCLEVEL varchar(11),
@ITEMPATTERN nvarchar(31) = '%',
@UOFM varchar(9) = '%'
)
RETURNS @retTable TABLE
(
[ITEMNMBR] [varchar](31) primary key NOT NULL,
[BREAK1] [varchar](255) NOT NULL,
[PRICE1] [numeric](19, 5) NULL,
[BREAK2] [varchar](255) NOT NULL,
[PRICE2] [numeric](19, 5) NULL,
[BREAK3] [varchar](255) NOT NULL,
[PRICE3] [numeric](19, 5) NULL,
[BREAK4] [varchar](255) NOT NULL,
[PRICE4] [numeric](19, 5) NULL,
[BREAK5] [varchar](255) NOT NULL,
[PRICE5] [numeric](19, 5) NULL,
[BREAK6] [varchar](255) NOT NULL,
[PRICE6] [numeric](19, 5) NULL
)
AS
BEGIN

WITH PriceTable
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr like @ITEMPATTERN and PRCLEVEL= @PRCLEVEL
AND CURNCYID=@CURNCYID AND UOFM LIKE @UOFM
)
INSERT @retTable
select itemnmbr,
isnull(max(case when PriceTable.[RowNumber] = 1 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break1
, max(case when PriceTable.[RowNumber] = 1 then
uomprice end) as Price1
, isnull(max(case when PriceTable.[RowNumber] = 2 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break2
, max(case when PriceTable.[RowNumber] = 2 then
uomprice end) as Price2
,isnull( max(case when PriceTable.[RowNumber] = 3 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break3
, max(case when PriceTable.[RowNumber] = 3 then
uomprice end) as Price3
,isnull( max(case when PriceTable.[RowNumber] = 4 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break4
, max(case when PriceTable.[RowNumber] = 4 then
uomprice end) as Price4
, isnull(max(case when PriceTable.[RowNumber] = 5 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break5
, max(case when PriceTable.[RowNumber] = 5 then
uomprice end) as Price5
, isnull(max(case when PriceTable.[RowNumber] = 6 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break6
, max(case when PriceTable.[RowNumber] = 6 then
uomprice end) as Price6
from PriceTable
group by itemnmbr, curncyid, prclevel, UOFM;

RETURN

END;
 

Putting it to work

Now it is a table valued function, this allows a crosstabbed price table to be used as if it were a table. For example to add in the item description from the item master table IV00101, the following is used;

 
SELECT 
IV00101.ITEMDESC,
PricesCrossTab.*
From Extract_PricesCrosstabTable('Z-US$','RETAIL','WIRE%','Foot') PricesCrossTab
JOIN IV00101
ON PricesCrossTab.ITEMNMBR= IV00101.ITEMNMBR

Results of join with IV00101 showing description pulled in

The unit of measure has been used as a parameter here for selection, however by changing the schema of the table valued function returned table type to include unit of measure as part of the primary key, all units of measure can be returned. This is the foundations of some scripts that can be amended to produce the results that you require for your particular circumstances.

The Regulator regulator expression tool

The regulator is a great tool for building and testing regular expressions that I have been using for many years now. I know I should move on (see end of post), however it is familiar and does what I need.

Problem starting TheRegulator

Sometimes when it is not closed correctly it can stop working on start up. The form Ctor fails. The exception is something like, “

"Application has generated an exception that could not be handled.”

If this happens navigate to the application directory and remove the settings file in the application directory and all will be well again. A new settings file will be created.

This is a common problem I have with settings files myself. They can cause the application to fail at start up if they get corrupted, that is surprisingly common with workers wanting to get home quicker by turning off the PC before it has finished shutdown.

http://sourceforge.net/projects/regulator/

image

 

The developer community seem to like http://www.regexbuddy.com/

This is a paid for app unlike The Regulator. RegexBuddy gets good things said about it though.