Queries (Specification Pattern)

• Queries are built on the extended specification pattern. 
  • The specification pattern extensions bring better experience especially for filtering and nesting the filters. 
  • These extensions also cover data sorts. 
• Use ISpecification<TEntity> or ISpecificationWithParams<TEntity> (for final queries) as a return type instead of D3Specification<TEntity> or D3SpecificationWithParams<TEntity>. 
• For better performance and less architecture impact, do not forget to keep prebuilt queries unchanged and static as possible. 
  • QueryContainer will help you with that. 
• Beware of correct placement of parentheses not to get unwanted joins or filtering in query strings. see more... 

Important Note:  If a specification is not prebuilt and you join tables or filter or sort data into it, then the clone (copy) of original specification is made to keep the original query unchanged. But it is done only for the first time, if you want to reuse the new specification clone, call specification.Prebuild()

Query Building (Join)

• Start with D3Specification.Create<TEntity>(D3Context, forceInnerJoin = false, forceLeftJoin = false) to create single table query (SELECT). see more... 
• Join tables by And(ISpecification) or by Or(ISpecification) where ISpecification is a base type for ISpecification<TEntity> where TEntity is any generic type limited to class and new(). 
• If the parent type has 2 or more references to the joining (child) type and you do not want to include all the references, you can also use the overload of D3Specification.Create<TEntity>(ID3Context, bool, bool)
  
     // Let's assume that we have Match with 2 references to Team - HomeTeam and AwayTeam and we want to include both references.
     D3Specification.Create<Match>(D3Context).And(D3Specification.Create<Team>(D3Context));
• If you want to include a specific property only, use the overload with lambda expression where you specify propToIncludeOnly - the only reference that is related to the property is included. 
  
     // Let's assume that we have Match with 2 references to Team - HomeTeam and AwayTeam and we want to include HomeTeam only.
     D3Specification.Create<Match>(D3Context).And(D3Specification.Create(D3Context, (Match x) => x.HomeTeam));
• Beware of joining a table which is not accessible from the original query (already joined) tables - an error will be thrown. 
  • This can also happen to you if you do not place parentheses correctly. 
• The third overload Create<TInverseRoot, TResult>(..) is used if you want to build a query from a collection side, but you want to get some item's relation as a root result. It is useable if you want to sort by a collection. sorting by a collection

Query Building (Filter)

• Use CompareFormat(..) function to create a placeholder of a comparison for a property. The placeholder is substituted by a parameter constant when building final SQL. 
  • If you call D3Specification.BuildSQL(spec, bool, bool, parameters) and you attach no parameter for a specific integer key, the comparison is omitted. 
• Use Convert.DBNull to compare a property with NULL. 
• Use Or() when you specified if the child query contains a filter that you want to apply for reference 1 or reference 2. 
• Use Compare(..) to set the filter argument value for a the property directly. see more... 
• Parameter ComparisonOp op:  Specifies the comparison-like operator of the comparison. 
• Parameter LogicalOp appendWithOp:  Specifies which operator will be used when appending the comparison to an existing (original) filter. 
  • i.e.: Use LogicalOp.AND | LogicalOp.DuplicitOr If you join a child table that is referenced twice or more times without usage of propToIncludeOnly. The filter will be appended as AND (prop1 .. OR prop2 .. )

!!! IMPORTANT !!! Nesting filters using And() and Or() functions takes precedence over appendWithOp as they usually cover a whole expression by parentheses if the (original) current "nested" level filter's logical operators differ from the operator you add by And() or Or() functions. 

First filter item of the expression is appended with no logical operator. 

• Use Not() to negate top filter context. 
• Use In() or NotIn() to create "IN" or "NOT IN" filter expression on id column. see more... 

Query Building (Sort)

• Use OrderBy() function to specify which column to sort by. 
• Parameter uint sortOrder:  Specifies the order of the column to sort. The value for the first column order is 1. 
  • If you specify OrderBy(..) for a same property, then it is overridden if the original sortOrder is higher (worse position) or replaceOldSortOrder is true. 
  • If you do not specify sortOrder then the offset of 10 + number of sort columns already used is set to evaluate the sortOrder. 
• Do not use collection properties before its parent properties, if you do so, parents' id properties will be placed before collection properties due to performance (Entity Framework does the same). To sort from a collection side use "inverse query" sorting by a collection. 

Query Building (DTOs)

• D3ORM supports returning DTO query results directly from the database - just use ISpecification.AsDTO(..) on the top entity and optionally on the other joined entities if you need to specify specific name exceptions. 
• Example of Complex Filter and Complex Join Query
  
     D3Specification.Create<Match>(D3Context)
     .AsDTO(new Tuple<Expression<Func<Match, object?>>, Expression<Func<MatchTestDTO, object?>>>(x => x.WinnerId, y => y.WinnerIdTest))
     .And(QC.TournamentBaseQuery
         .In(QC.TournamentBaseQuery
             .NotIn(y => y.TournamentId, new int[] { 3, 4 }, LogicalOp.AND)
             .And(QC.TeamIdFilterQuery(null).Compare(y => y.RegistrationDate, ComparisonOp.GREATER_THAN_OR_EQUAL, new DateTime(2021, 1, 1)))))
     .And(QC.TeamBaseQuery
         .And(D3Specification.Create<User>(D3Context)
             .AsDTO<UserDTO>())
         .Or(D3Specification.Create<TournamentTeam>(D3Context).Compare(x => x.TournamentTeamId, ComparisonOp.EQUALS, 1).Compare(x => x.TournamentTeamId, ComparisonOp.EQUALS, 2, LogicalOp.OR)))
     .Or(QC.TeamEmptyQuery.Compare(x => x.Name, ComparisonOp.LIKE, "noname%").OrderBy(x => x.Name, true, 1)
         .And(D3Specification.Create<TournamentTeam>(D3Context))
         .And(D3Specification.Create<Tournament>(D3Context).Compare(x => x.Name, ComparisonOp.LIKE, "noname%", LogicalOp.AND)))
     .And(QC.TournamentBaseQuery.OrderBy(x => x.Name, false, 2))
     .AsDTO<PlayoffRoundCoupleDTO>()

Building DTO Queries Benefits: 

• Building DTO lists directly from the query: Less server side conversions between entities and DTOs. 
• Omitting and renaming of the property names allowed. 

Building DTO Queries Constraints:

1. All DTOs of a single instance (with aggregates) must be placed into the same namespace or defined in some namespace from ID3Context.OtherModelNamespaces. 
2. If any lower part of the specification is annotated with AsDTO, the top specification part should be too. 
3. DTOs do not have to remain all properties, but must remain id properties - you can use JsonIgnoreAttribute not to include them in a HTTP response. 
4. DTO names must contain its entity name. 
5. Id property names must remain the same as the entity name or at least should follow the id property pattern with a DTO name containing the original entity name instead of the entity name itself (the same for navigation properties). 
   • Use original entity name, or entity name with a suffix defined by ID3Context.DtoPatternFormat. 
   • i.e.: PlayoffRoundDTO, MatchDTO... or i.e.: PlayoffRoundDTO, Match; i.e.: TournamentTeamRank, TournamentTeamStatRank or i.e.: TournamentTeamRank, TournamentTeamStatDTO.. 
6. The namespace and DTO name constraints do not have to applied for DTOs created at the server side (after fetching all the data from a database). 

Query Building (PreBuild)

• To reuse a single instance of a specification clone it or prebuild it. 
• After you start building a specification it is clonned since the second building method is called, but also the returned result can be used only once without CloneUnsafe(..) or PreBuild(..)

Query Building (Special Cases)

Count Rows

• Use CountRows() to create a "count rows" query SELECT count(id)... 

Skip and Take

AVG, COUNT, MIN, MAX, SUM

• To build computing aggregation function use one of theese: 
• To do more complex operation across more columns use CustomAggFunc(..) or CustomChildAggFunc(..) and optionally apply AVG, COUNT, MIN, MAX or SUM. see an example... 
  
  ISpecification<TEntity> Avg(int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties);
  ISpecification<TEntity> Avg<TParent, TChild>(Expression<Func<TParent, TChild>> parentProp, int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties) where TParent : class where TChild : class;
  
  ISpecification<TEntity> Count(int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties);
  ISpecification<TEntity> Count<TParent, TChild>(Expression<Func<TParent, TChild>> parentProp, int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties) where TParent : class where TChild : class;
  
  ISpecification<TEntity> Min(int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties);
  ISpecification<TEntity> Min<TParent, TChild>(Expression<Func<TParent, TChild>> parentProp, int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties) where TParent : class where TChild : class;
  
  ISpecification<TEntity> Max(int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties);
  ISpecification<TEntity> Max<TParent, TChild>(Expression<Func<TParent, TChild>> parentProp, int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties) where TParent : class where TChild : class;
  
  ISpecification<TEntity> Sum(int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties);
  ISpecification<TEntity> Sum<TParent, TChild>(Expression<Func<TParent, TChild>> parentProp, int? propertySortOrder = null, bool includeAllByDefault = false, params Expression<Func<TEntity, IComparable?>>[] properties) where TParent : class where TChild : class;

Inverse Query (Sorting by a Collection)

• If you want to sort by a collection you have to build the query from a collection side and define so called "inverse root" query, otherwise all root (parent) ids will be placed before the first collection property in the sort clause. 
• Let's say we want to sort TournamentTeams by its statistics from a specific tournament phase and we have more statistics for each TournamentTeam (one for each phase). 
  • The result is a TournamentTeam list sorted by TournamentTeamStats. 
  • This can be used to make play-off couples. 
    
       D3Specification.Create(d3Context, (TournamentTeam x) => x.TournamentTeamStats, (TournamentTeamStat x) => x.TournamentTeamId)
        .And(D3Specification.Create<TournamentTeamStat>(d3Context)
            .OrderBy(x => x.PhasePoints, true, 3)
            .CustomAggFunc(nameof(TournamentTeamStatRank.SetsWonLostDiff), ComputeType.NONE, x => x.SetsWon, CrossColumnOp.DiffOrNone, x => x.SetsLost, propertySortOrder: 4)
            .CustomAggFunc(nameof(TournamentTeamStatRank.ScorePlusMinus), ComputeType.NONE, x => x.ScorePlus, CrossColumnOp.DiffOrNone, x => x.ScoreMinus, propertySortOrder: 5)
            .CompareFormat(x => x.TournamentPhase, ComparisonOp.EQUALS, 1)
            .OrderBy(x => x.TournamentPhase, false, 1))
        .And(D3Specification.Create<TournamentTeam>(d3Context)
            .CompareFormat(x => x.TournamentId, ComparisonOp.EQUALS, 0)
            .And(D3Specification.Create<Team>(d3Context))
            .OrderBy(x => x.BasicGroupName, false, 2))
        .IncludeSelectProps((TournamentTeamStat x) => x.PhasePoints, (TournamentTeamStat x) => x.TournamentPhase, (TournamentTeamStat x) => x.LastChange)
        .IncludeSelectProps((TournamentTeam x) => x.BasicGroupName, (TournamentTeam x) => x.LastChange)
        .IncludeSelectProps((Team x) => "*")
        .AsDTO<TournamentTeamRank>();

Execute Query

• As you have all queries static and prebuilt using QueryContainer use the an extension from Data Reading Extensions to read the data from a database. 

Debug Mode

• To see all the columns AS identifiers and to pretty print the select part use debugMode: true

Stored Procedures

• Stored Procedures can be called directly from ICommonRepository as ExecuteStoredSelect(..) or as ExecuteStoredCompute(..)
• You may want to know the naming rules of the columns of stored procedures to work with D3ORM. 
• Always use CSharp entities and property names in the AS column identifiers... 
• Entity aggregate root name is omitted from the column name, so the root column names looks like '.RootPropertyName'
• Aggregate child must specify all the path of the entity's property you want to assign the value to, excluding entity aggregate root. 
• i.e.: 'DirectChild.DirectChildPropertyName' or 'DirectRootChild.GrandParent.Parent.Child.ChildPropertyName'
• If a navigation property name is not the same as a property type, use OneToManyPropertyName[GenericTypeName] for "one to many" relations (collections) or ManyToOnePropertyName(PropertyTypeName) for "many to one" relations. 
  • (In case of "one to many" relations, you have to do it always even if you do not pluralize the property type, because the property name reflects the entity name it contains, but the type name looks like: ICollection<TEntity> in this case.)
• If you build a query from a collection side and there is no reference to the child query result, use the navigation property like you would specify the inverseRootCollection parameter as following: inverseRootPropertyName[inverseRootCollectionItemType]. &lt;InverseResultColumnName&gt; . 
• Compute Aggregation Functions has the format of: '<shortcut> <CSharpPropertyFullPath>'
• List of Compute Shortcuts: 
  • CNT := COUNT
  • AVG := Average
  • MIN := Minimal Value
  • MAX := Maximal Value
  • SUM := SUMA
• i.e.: 'CNT DirectChild.GrandParent.Parent.Child.ChildPropertyName'

If you want to get high performance of the query or you do not want to include columnAsIdentifiers into the query, define them into List<string> and attach them when calling AggSelect(..) or AggCompute(..) or ExecuteStoredSelect(..) or ExecuteStoredCompute(..). 

Help yourself by specifying a specification with the same query result structure in order to retrieve the column columnAsIdentifiers and optionally put them into a static or singleton List<string>. 

SP Call Expression

Parentheses and Correct Alignment

Keep nice alignment of specifications to make life easier... 

• Type each specification context to a new line and align it with the tabulator as deep as the level of nesting is. 
  • You can keep filter or sort functions on the same line except In, NotIn, InFormat, NotInFormat, Not and computing aggregate functions. 
• As you can see in the example below, any closing parenthesis on any line end associate a parenthesis of a context start which is nested on the same level as the line after the last closing parenthesis. 
  • In other words, more closing parentheses at the end of a line means more closed contexts and more level down of nesting on the next line. 

Example of Incorrect Placement of Parentheses