CODEWITHSUNDEEP
asp.netentitydatasource
Martin
Martin

Reputation: 55

Using DISTINCT with an EntityDataSource

I have an entity datasource (connetced to a SQL Server 2012 database) on a .NET v4 aspx page. My datasource declaration looks like this:

<asp:EntityDataSource ID="BlahBlah" runat="server" 
    ConnectionString="name=BlahBlah" 
    DefaultContainerName="BlahBlah" EnableFlattening="False" 
    EntitySetName="vw_shop_ProductListWithIndivCats" 
    Where="it.Display =true" OrderBy="it.[DisplayPriority]" 
    Select="DISTINCT it.[ProductID], it.[Name], it.[Short_rubric],  it.[Price],  it.[Available], it.[Display], it.[ProductDetailLayoutID], it.[DisplayPriority] " >

The WHERE value is modified in code when the user interacts with the site. The entity has more columns than in the SELECT statement

The data is displayed in a gridview and that works just fine except the DISTINCT keyword is not being respected, i.e. duplicate rows from the tables are being displayed in the gridview.

Any suggestions?

Some more details of the question. The columns in the entity (which is a view) vw_shop_ProductListWithIndivCats are:

ProductID
Name
Short_rubric
Rubric
Price
Postage
Image_small
Image_Large
ImageType
Available
Display
ProductDetailLayoutID
DisplayPriority
CategoryID
Category

Most of the fields are derived form a products table. In the view there is a many to many join to a Categories table with the last two fields in mediated through the usual linking table.

In the user interface the user may selct products by categories and as products may have multiple categories, say n categopries, the selct statement (without the distinct) produces x rows which as the view only contains fields form the products table will contain n duplicate rows. I was expecting either DISTINCT or GROUP BY to dedupe the rows but neither does. I have conformed that I have the columns I think I have by temporarily putting in a list voiew that displays every column

Upvotes: -1

Views: 1663

Answers (1)

Tom Chantler
Tom Chantler

Reputation: 14951

Why not try adding a GROUP BY clause which contains all of your columns? MSDN Link And here is a link to the main EntityDataSource article

As you probably know, in this case both GROUP BY and DISTINCT are likely to produce the ame query plan in SQL Server.

So you would change it to be like this (drop the DISTINCT):

<asp:EntityDataSource ID="BlahBlah" runat="server" 
    ConnectionString="name=BlahBlah" 
    DefaultContainerName="BlahBlah" EnableFlattening="False" 
    EntitySetName="vw_shop_ProductListWithIndivCats" 
    Where="it.Display =true" OrderBy="it.[DisplayPriority]" 
    Select="it.[ProductID], it.[Name], it.[Short_rubric],  it.[Price],  it.[Available], [Display], it.[ProductDetailLayoutID], it.[DisplayPriority]"
    GroupBy="it.[ProductID], it.[Name], it.[Short_rubric],  it.[Price],  it.[Available], it.[Display], it.[ProductDetailLayoutID], it.[DisplayPriority]" />

Upvotes: 1

Related Questions