QuerySpec generating DISTINCT

Posts   
 
    
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 11-Jul-2016 16:38:18   

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



Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# 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
Posts: 434
Joined: 23-Jan-2005
# Posted on: 23-Jul-2016 13:08:36   

Oops, you are correct that the code and query don't match. flushed 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)


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 avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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.

Frans Bouma | Lead developer LLBLGen Pro