Is there another way to code this so that the DQE is not generating DISTINCT?
var qf = new QueryFactory();
var dataItems = new List<CustomerOrderModel>();
var query = qf.Create()
.From(qf.Customer
.InnerJoin(qf.Order)
.On(CustomerFields.CustomerId == OrderFields.CustomerId)
.InnerJoin(qf.Employee)
.On(OrderFields.EmployeeId == EmployeeFields.EmployeeId))
//map entity to model class
.Select(() => new CustomerOrderModel()
{
OrderId = OrderFields.OrderId.ToValue<int>(),
OrderDate = OrderFields.OrderDate.ToValue<DateTime>(),
RequiredDate = OrderFields.RequiredDate.ToValue<DateTime>(),
ShippedDate = OrderFields.ShippedDate.ToValue<DateTime>(),
CustomerId = CustomerFields.CustomerId.ToValue<string>(),
CompanyName = CustomerFields.CompanyName.ToValue<string>(),
EmployeeId = EmployeeFields.EmployeeId.ToValue<int>(),
SalesRepLastName = EmployeeFields.LastName.ToValue<string>(),
SalesRepFirstName = EmployeeFields.FirstName.ToValue<string>(),
Freight = OrderFields.Freight.ToValue<decimal>(),
ShipCity = OrderFields.ShipCity.ToValue<string>(),
ShipRegion = OrderFields.ShipRegion.ToValue<string>(),
ShipCountry = OrderFields.ShipCountry.ToValue<string>(),
ShipVia = OrderFields.ShipVia.ToValue<int>(),
})
.Where(CustomerFields.Country == "USA")
.OrderBy(OrderFields.OrderDate.Descending(), CustomerFields.CompanyName.Ascending())
.Offset(90)
.Limit(20);
using (var dataAdapter = new DataAccessAdapter())
{
dataItems = dataAdapter.FetchQuery<CustomerOrderModel>(query);
}
Generated SQL
SELECT DISTINCT [Northwind].[dbo].[Customers].[CompanyName],
[Northwind].[dbo].[Customers].[ContactName],
[Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],
[Northwind].[dbo].[Employees].[FirstName],
[Northwind].[dbo].[Employees].[LastName],
[Northwind].[dbo].[Employees].[EmployeeID] AS [EmployeeId],
[Northwind].[dbo].[Orders].[Freight],
[Northwind].[dbo].[Orders].[OrderDate],
[Northwind].[dbo].[Orders].[OrderID] AS [OrderId],
[Northwind].[dbo].[Orders].[RequiredDate],
[Northwind].[dbo].[Orders].[ShipCity],
[Northwind].[dbo].[Orders].[ShipCountry],
[Northwind].[dbo].[Orders].[ShippedDate],
[Northwind].[dbo].[Orders].[ShipRegion],
[Northwind].[dbo].[Orders].[ShipVia]
FROM (( [Northwind].[dbo].[Customers]
INNER JOIN [Northwind].[dbo].[Orders]
ON [Northwind].[dbo].[Customers].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID])
INNER JOIN [Northwind].[dbo].[Employees]
ON [Northwind].[dbo].[Employees].[EmployeeID] = [Northwind].[dbo].[Orders].[EmployeeID])
WHERE (([Northwind].[dbo].[Customers].[Country] = @p1))
ORDER BY [Northwind].[dbo].[Orders].[OrderID] ASC