Solution to Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Question

Reason for Error: I am creating a method on a TimeCard entity. The TimeCard entites relates to an Employee entity. The method sorts TimeCards by employee name.

Obvious Solution Copy the stored procedure from the [project_name]_procedures.sql file and add the sorted columns to the select statement. Then create a raw method using the stored procedure.

Question: Is there a better solution. I don't like the obvious solution because it will break if I add additional properties to the TimeCard entity.

Answer 1

The SQL generated by CFE is not valid as explain in this post: How SQL DISTINCT and ORDER BY are Related. I think there are 2 workarounds:

1. Remove the DISTINCT from the stored procedure if it is not needed:

   <cf:method name="LoadForActiveEmployeesSortedByEmployee" distinct="false" ... />
   

2. Use a raw method with the valid SQL query:

   <cf:method body="LOAD() raw" rawBody="SELECT DISTINCT $TimeCard{Columns}$, EmployeeName FROM $TimeCard$ JOIN ... WHERE ... ORDER BY ...">
   

Answer 2

I came up with an exceptable solution which I am sharing. If there is a better solution please let me know.

Why this error occurs: When using CFQL and adding a WHERE clause that uses properties from a related entity the generated stored procedure adds DISTINCT to the SELECT statement. This will generate an error if you also add a sort order based on a property of the related entity.

Step 1

Create the Method using CFQL without a sort order

Step 2

On the BOM Producer, set the NameSpaceImports proterty to System.Linq

Step 3

Create a Snipped Method that uses LINQ to sort by the related entities property

Caveat

When calling the method the return type is List not TimeCardCollection

Below is the full xml definition. To reproduce the original error, set enabled to true for the method LoadForActiveEmployeesSortedByEmployee

<cf:project defaultNamespace="DemoSortByRelatedEntity" xmlns:cf="http://www.softfluent.com/codefluent/2005/1" xmlns:cfx="http://www.softfluent.com/codefluent/modeler/2008/1" xmlns:cfps="http://www.softfluent.com/codefluent/producers.sqlserver/2005/1" xmlns:cfom="http://www.softfluent.com/codefluent/producers.model/2005/1" xmlns:cfasp="http://www.softfluent.com/codefluent/producers.aspnet/2011/1" defaultTargetFramework="4.0" defaultConnectionString="Database=DemoSortByRelatedEntity;Integrated Security=true" createDefaultMethodForms="true" createDefaultApplication="false" createDefaultHints="false">
  <cf:import path="Default.Surface.cfp" />
  <cf:producer name="SQL Server Producer" typeName="CodeFluent.Producers.SqlServer.SqlServerProducer, CodeFluent.Producers.SqlServer">
    <cf:configuration connectionString="Database=DemoSortByRelatedEntity;Integrated Security=true;Server=.\SQLExpress" produceViews="true" targetVersion="Sql2014" targetDirectory="..\DemoSortByRelatedEntity.Persistence" cfx:targetProjectLayout="UpdateItems, DontRemove" cfx:targetProject="..\DemoSortByRelatedEntity.Persistence\DemoSortByRelatedEntity.Persistence.dbproj" />
  </cf:producer>
  <cf:producer name="BOM Producer" typeName="CodeFluent.Producers.CodeDom.CodeDomProducer, CodeFluent.Producers.CodeDom, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1bb6d7cccf1045ec">
    <cf:configuration compileWithVisualStudio="true" targetDirectory="..\DemoSortByRelatedEntity" cfx:targetProject="..\DemoSortByRelatedEntity\DemoSortByRelatedEntity.csproj" namespaceImports="System.Linq" cfx:targetProjectLayout="Update" />
  </cf:producer>
  <cf:producer name="ASP.NET WebForms Producer" typeName="CodeFluent.Producers.UI.AspNetProducer, CodeFluent.Producers.UI">
    <cf:configuration categoryPath="UI\AspNetWebForms" targetDirectory="..\DemoSortByRelatedEntity.Web" cfx:targetProject="..\DemoSortByRelatedEntity.Web\DemoSortByRelatedEntity.Web.csproj" />
  </cf:producer>
  <cf:entity name="TimeCard" namespace="DemoSortByRelatedEntity">
    <cf:property name="Id" key="true" />
    <cf:property name="InTime" typeName="datetime" />
    <cf:property name="OutTime" typeName="datetime" />
    <cf:property name="Employee" typeName="{0}.Employee" />
    <cf:method name="LoadForActiveEmployeesSortedByEmployee" body="LOAD() WHERE Employee.IsActive = TRUE ORDER BY Employee.Name" enabled="false" />
    <cf:method name="LoadForActiveEmployees" body="LOAD() WHERE Employee.IsActive = TRUE" />
    <cf:instance>
      <cf:instanceValue name="Id">58138358-1578-4f3a-9273-4fea17aa7dd5</cf:instanceValue>
      <cf:instanceValue name="InTime">1/1/2019 12:00:00 AM</cf:instanceValue>
      <cf:instanceValue name="OutTime">2/2/2019 12:00:00 AM</cf:instanceValue>
      <cf:instanceValue name="Employee">a57ba60a-5e6a-4df0-88b9-283d60517b66</cf:instanceValue>
    </cf:instance>
    <cf:instance>
      <cf:instanceValue name="Id">487a8bcf-89a1-49d6-b970-ae8d32c0f7f2</cf:instanceValue>
      <cf:instanceValue name="InTime">2/3/2019 12:00:00 AM</cf:instanceValue>
      <cf:instanceValue name="OutTime">2/4/2019 12:00:00 AM</cf:instanceValue>
      <cf:instanceValue name="Employee">4762eeea-dcc7-4d7e-8b07-1f78b73657ed</cf:instanceValue>
    </cf:instance>
    <cf:instance>
      <cf:instanceValue name="Id">5f039986-e423-460f-aa7e-9b28e2a507b3</cf:instanceValue>
      <cf:instanceValue name="InTime">2/5/2019 12:00:00 AM</cf:instanceValue>
      <cf:instanceValue name="OutTime">2/6/2019 12:00:00 AM</cf:instanceValue>
      <cf:instanceValue name="Employee">c7ae6667-55d4-4ba2-8576-4c0c950b7231</cf:instanceValue>
    </cf:instance>
    <cf:setSnippet name="LoadForActiveEmployeesSortedByEmployeeFix1" language="CSharp"><![CDATA[
    public static System.Collections.Generic.List<TimeCard> LoadForActiveEmployeesSortedByEmployeeFix1()
        {
            return LoadForActiveEmployees().OrderBy(x => x.Employee.Name).ToList();
        }
]]></cf:setSnippet>
  </cf:entity>
  <cf:entity name="Employee" namespace="DemoSortByRelatedEntity">
    <cf:property name="Id" key="true" />
    <cf:property name="Name" entityDisplay="true" />
    <cf:property name="IsActive" typeName="bool" />
    <cf:instance>
      <cf:instanceValue name="Id">a57ba60a-5e6a-4df0-88b9-283d60517b66</cf:instanceValue>
      <cf:instanceValue name="IsActive">True</cf:instanceValue>
      <cf:instanceValue name="Name">Jon</cf:instanceValue>
    </cf:instance>
    <cf:instance>
      <cf:instanceValue name="Id">4762eeea-dcc7-4d7e-8b07-1f78b73657ed</cf:instanceValue>
      <cf:instanceValue name="Name">Mike</cf:instanceValue>
    </cf:instance>
    <cf:instance>
      <cf:instanceValue name="Id">c7ae6667-55d4-4ba2-8576-4c0c950b7231</cf:instanceValue>
      <cf:instanceValue name="IsActive">True</cf:instanceValue>
      <cf:instanceValue name="Name">Lisa</cf:instanceValue>
    </cf:instance>
  </cf:entity>
</cf:project>