CODEWITHSUNDEEP
asp.netentity-frameworkjoin
Fanda
Fanda

Reputation: 3786

Joins using EntityDataSource

I want to select data using EntityDataSource control on ASP.NET page. Parameters given to data source are set by this code:

dsPlastics.Select = "top(10) it.PlasticId, it.Name, it.DateModified, it.Producer.Name as ProducerName, it.PlasticType.PlasticFamily.Name as PlasticFamilyName, it.PlasticType.Name as PlasticTypeName";
dsPlastics.Where = "it.Active == true";
dsPlastics.OrderBy = "it.DateModified DESC";

Im am expecting query similar to this (selects data as I need - last 10 records joined with related data):

select top(10) PlasticId, pl.Name, DateModified, pr.Name as ProducerName, pf.Name as PlasticFamilyName, pt.Name as PlasticTypeName
from Plastics pl
left join Producers pr ON pl.ProducerId = pr.ProducerId
left join PlasticTypes pt ON pl.PlasticTypeId = pt.PlasticTypeId
left join PlasticFamilies pf ON pt.PlasticFamilyId = pf.PlasticFamilyId
where pl.Active = 1
order by pl.DateModified DESC

But entity framework generates this (does not select data as I need):

SELECT 
[Limit1].[PlasticId] AS [PlasticId], 
[Limit1].[Name] AS [Name], 
[Limit1].[DateModified] AS [DateModified], 
[Extent2].[Name] AS [Name1], 
[Extent4].[Name] AS [Name2], 
[Extent5].[Name] AS [Name3]
FROM (SELECT TOP (10) [Extent1].[PlasticId] AS [PlasticId], [Extent1].[ProducerId] AS [ProducerId], [Extent1].[PlasticTypeId] AS [PlasticTypeId], [Extent1].[Name] AS [Name], [Extent1].[DateModified] AS [DateModified]
    FROM [dbo].[Plastics] AS [Extent1]
    WHERE [Extent1].[Active] = 1 ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Producers] AS [Extent2] ON [Limit1].[ProducerId] = [Extent2].[ProducerId]
LEFT OUTER JOIN [dbo].[PlasticTypes] AS [Extent3] ON [Limit1].[PlasticTypeId] = [Extent3].[PlasticTypeId]
LEFT OUTER JOIN [dbo].[PlasticFamilies] AS [Extent4] ON [Extent3].[PlasticFamilyId] = [Extent4].[PlasticFamilyId]
LEFT OUTER JOIN [dbo].[PlasticTypes] AS [Extent5] ON [Limit1].[PlasticTypeId] = [Extent5].[PlasticTypeId]
ORDER BY [Limit1].[DateModified] DESC

Please, how should I build the query, to get query which I need?

Upvotes: 2

Views: 2336

Answers (1)

sudhansu63
sudhansu63

Reputation: 6200

You can try like this.

<asp:EntityDataSource ID="ProductDataSource" runat="server" 
    CommandText="select top(10) PlasticId, pl.Name, DateModified, pr.Name as ProducerName, pf.Name as PlasticFamilyName, pt.Name as PlasticTypeName
from Plastics pl
left join Producers pr ON pl.ProducerId = pr.ProducerId
left join PlasticTypes pt ON pl.PlasticTypeId = pt.PlasticTypeId
left join PlasticFamilies pf ON pt.PlasticFamilyId = pf.PlasticFamilyId
where pl.Active = 1
order by pl.DateModified DESC"
    ConnectionString="name=AdventureWorksEntities" 
    DefaultContainerName="AdventureWorksEntities" >
</asp:EntityDataSource>

Or try setting your query in the Command Text property

Upvotes: 2

Related Questions