Forum:  Bugs & Issues

Thread:  QuerySpec generating DISTINCT


jovball (User)   Posted on: 11-Jul-2016 16:38:18.
Is there another way to code this so that the DQE is not generating DISTINCT?

Code:

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
Code:

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


Walaa (Support Team)   Posted on: 11-Jul-2016 22:25:15.
I see you are using LLBLGen v.4.2, which runtime library version are you using?
Please check the forum guidelines for more details on how to get the version number.
http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7717

Are you sure this is the final executed query, as I don't see the paging constructs?
Do you still receive back the exact amount of records as specified by the limit?


jovball (User)   Posted on: 23-Jul-2016 13:08:36.
Oops, you are correct that the code and query don't match. Embarrassed I was testing several different things and captured a query for a typed view rather than the one for the QuerySpec code.

That QuerySpec code is producing this query (which is correct)

Code:

SELECT [Northwind].[dbo].[Orders].[OrderID] AS [OrderId],
[Northwind].[dbo].[Orders].[OrderDate],
[Northwind].[dbo].[Orders].[RequiredDate],
[Northwind].[dbo].[Orders].[ShippedDate],
[Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],
[Northwind].[dbo].[Customers].[CompanyName],
[Northwind].[dbo].[Employees].[EmployeeID] AS [EmployeeId],
[Northwind].[dbo].[Employees].[LastName],
[Northwind].[dbo].[Employees].[FirstName],
[Northwind].[dbo].[Orders].[Freight],
[Northwind].[dbo].[Orders].[ShipCity],
[Northwind].[dbo].[Orders].[ShipRegion],
[Northwind].[dbo].[Orders].[ShipCountry],
[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].[Orders].[EmployeeID] = [Northwind].[dbo].[Employees].[EmployeeID])
WHERE ( ( [Northwind].[dbo].[Customers].[Country] = @p1))
ORDER BY [Northwind].[dbo].[Orders].[OrderDate] DESC,
[Northwind].[dbo].[Customers].[CompanyName] ASC
OFFSET 90 ROWS FETCH NEXT 20 ROWS ONLY

Otis (LLBLGen Pro Team)   Posted on: 24-Jul-2016 09:56:32.
To answer the original question: no. You're specifying a paging directive and also a join, which could lead to duplicates, so the engine issues a distinct as otherwise the paging makes little sense as it will return duplicate pages.