Dynamic mapping

Tip

Before read this topic be sure to already readied Glossary and Common mapping rules contains base information to better understanding this.

Dynamic mapping is defined using specific LINQ style expression as private static field of mapped class and it properties.

Dynamic mapping sample

Dynamic mapping example for same data object as represented in static mapping description:

SQL representation of following sample:

with recursive T (catalogue_id, code, name, parent_id) 
   as ((SELECT R.catalogue_id AS R_catalogue_id,
            R.code AS R_code,
            R.name AS R_name,
            R.parent_id AS R_parent_id
        FROM T_CATALOGUE R
        WHERE ((@FilterByRoot is not null
            and R.catalogue_id = @FilterByRoot)
            OR (@FilterByRoot is null
            and R.parent_id is null))) UNION (SELECT S.catalogue_id AS S_catalogue_id,
            S.code AS S_code,
            S.name AS S_name,
            S.parent_id AS S_parent_id
        FROM T_CATALOGUE S, T T
        WHERE S.parent_id = (T.catalogue_id)))
SELECT CF.CUSTOMER_ID AS CF_CUSTOMER_ID,
    CF.DOC_CUST_ID AS CF_DOC_CUST_ID,
    CF.DOC_CUST_TYPE_ID AS CF_DOC_CUST_TYPE_ID,
    CF.DOC_ID AS CF_DOC_ID,
    CT.CUSTOMER_ID AS CT_CUSTOMER_ID,
    CT.DOC_CUST_ID AS CT_DOC_CUST_ID,
    CT.DOC_CUST_TYPE_ID AS CT_DOC_CUST_TYPE_ID,
    CT.DOC_ID AS CT_DOC_ID,
    CAST((SELECT Sum(A.amount) AS A_amount
        FROM T_DOC_SPEC S
            JOIN T_DOC_SPEC_AMOUNT A ON A.DOC_SPEC_ID = S.DOC_SPEC_ID
        WHERE S.doc_id = S.doc_id) AS NUMERIC(17, 5)) AS doc_amount /* DocAmount */,
    D.doc_date AS D_doc_date /* DocDate */,
    D.doc_id AS D_doc_id /* DocId */,
    D.DOC_TYPE_ID AS D_DOC_TYPE_ID,
    D.generation AS D_generation /* Generation */,
    D.changed AS D_changed /* Changed */,
    D.author AS D_author /* Author */,
    DD.delivery_date AS DD_delivery_date /* DeliveryDate */,
    DD.delivery_type AS DD_delivery_type /* DeliveryType */,
    DD.DOC_ID AS DD_DOC_ID,
    DS.DOC_DOC_STATE_ID AS DS_DOC_DOC_STATE_ID,
    DS.DOC_ID AS DS_DOC_ID,
    DS.DOC_STATE_ID AS DS_DOC_STATE_ID,
    F.CODE AS F_CODE,
    F.CUSTOMER_ID AS F_CUSTOMER_ID,
    F.name AS F_name /* CustomerFrom */,
    H.H_doc_id AS H_H_doc_id,
    H.H_history_date AS H_H_history_date /* DocLastChange */,
    N.DOC_ID AS N_DOC_ID,
    N.numb AS N_numb /* DocNumb */,
    S.code AS S_code /* DocStateCode */,
    S.DOC_STATE_ID AS S_DOC_STATE_ID,
    S.name AS S_name /* DocState */,
    SC.DOC_ID AS SC_DOC_ID,
    SC.scan AS SC_scan /* Scan */,
    SR.DOC_ID AS SR_DOC_ID,
    SR.source AS SR_source /* Source */,
    ST.S_doc_id AS ST_S_doc_id,
    T.CODE AS T_CODE,
    T.CUSTOMER_ID AS T_CUSTOMER_ID,
    T.name AS T_name /* CustomerTo */
FROM T_DOC D
    JOIN T_DOC_DOC_STATE DS ON DS.doc_id = D.doc_id
    JOIN T_DOC_STATE S ON S.DOC_STATE_ID = DS.DOC_STATE_ID
    JOIN T_DOC_NUMBER N ON N.doc_id = D.doc_id
    JOIN T_DOC_CUST CF ON CF.doc_id = D.doc_id
    JOIN T_DOC_CATALOGUE DC ON DC.doc_id = D.doc_id
    JOIN T_CATALOGUE U ON DC.catalogue_id = U.catalogue_id
    JOIN T_CUSTOMER F ON F.CUSTOMER_ID = CF.CUSTOMER_ID
    JOIN T_DOC_CUST CT ON CT.doc_id = D.doc_id
    JOIN T_CUSTOMER T ON T.CUSTOMER_ID = CF.CUSTOMER_ID
    LEFT OUTER JOIN T_DOC_SCAN SC ON SC.doc_id = D.doc_id
    LEFT OUTER JOIN T_DOC_SOURCE SR ON SR.doc_id = D.doc_id
    LEFT OUTER JOIN T_DOC_DELIVERY DD ON DD.doc_id = D.doc_id
    LEFT OUTER JOIN (SELECT H.doc_id AS H_doc_id,
            H.history_date AS H_history_date
        FROM T_DOC_HISTORY H) H ON D.doc_id = H.H_doc_id
    JOIN (SELECT S.doc_id AS S_doc_id
        FROM T_DOC_SPEC_TYPE ST
            JOIN T_DOC_SPEC S ON S.DOC_SPEC_TYPE_ID = ST.DOC_SPEC_TYPE_ID
        WHERE ST.code = @FilterBySpecTypeCode) ST ON D.doc_id = ST.S_doc_id
WHERE D.DOC_TYPE_ID = (select doc_type_id
        from T_DOC_TYPE
        where code='INVOICE')
    AND CF.DOC_CUST_TYPE_ID = (select doc_cust_type_id
        from T_DOC_CUST_TYPE
        where code='FROM')
    AND CF.DOC_CUST_TYPE_ID = (select doc_cust_type_id
        from T_DOC_CUST_TYPE
        where code='TO')
    AND D.doc_id = @FilterByDocId
    AND DC.catalogue_id in (SELECT T.catalogue_id AS T_catalogue_id FROM T T)

Mapping sample:

public partial class Invoice: IDataObject
{
    public static CustomLogic<Invoice> TestCustomLogic;

    private static Expression<CustomMapping<Invoice>> _mapping = (
        () => XDataMapping.CustomMapping<Invoice>()
            .DataTable("T_DOC", "D", 
                x => x.DictFilter("T_DOC_TYPE", "doc_type_id", "code", "INVOICE"))
            .DataTable("T_DOC_DOC_STATE", "DS", "D", x => x.Link("D", "doc_id"))
            .DataTable("T_DOC_STATE", "S", x => x.Link("DS", "doc_state_id"))
            .DataTable("T_DOC_CATALOGUE", "DC", "D", x => x.Link("D", "doc_id"), 
                x => x.SubqueryFilter("catalogue_id", "UT").SetOperation(FilterOperation.In))
            .DataTable("T_CATALOGUE", "U", x => x.Link("DC", "catalogue_id"))
            .DataTable("T_DOC_NUMBER", "N", "D", x => x.Link("D", "doc_id"))
            .DataTable("T_DOC_CUST", "CF", "D", x => x.Link("D", "doc_id"), 
                x => x.DictFilter("T_DOC_CUST_TYPE", "doc_cust_type_id", "code", "FROM"))
            .DataTable("T_CUSTOMER", "F", x => x.Link("CF", "customer_id"))
            .DataTable("T_DOC_CUST", "CT", "D", x => x.Link("D", "doc_id"), 
                x => x.DictFilter("T_DOC_CUST_TYPE", "doc_cust_type_id", "code", "TO"))
            .DataTable("T_CUSTOMER", "T", 
                x => x.Link("CT", "customer_id"))
            .DataTable("T_DOC_SCAN", "SC", "D", 
                x => x.Link("D", "doc_id").SetOperation(FilterOperation.OuterJoin))
            .DataTable("T_DOC_SOURCE", "SR", "D", 
                x => x.Link("D", "doc_id").SetOperation(FilterOperation.OuterJoin))
            .DataTable("T_DOC_DELIVERY", "DD", "D", 
                x => x.Link("D", "doc_id").SetOperation(FilterOperation.OuterJoin))
            .Subquery("A", typeof(DocSpecAmounts), "Amount", DataGrouping.Sum, 
                x => x.SubqueryLink("DocId"))
            .Subquery<CatalogueTree>("UT", x => x.CatalogueId, DataGrouping.None)
            .InnerView("H", XDataMapping.GetStructure("H", DataStructureFlag.Grouping)
                .DataTable("T_DOC_HISTORY", "H")
                .Select(x => new {
                    DocId = x.Field<long?>("H", string.Empty, 
                        z => z.Group(DataGrouping.None)),
                    HistoryDate = x.Field<DateTime?>("H", string.Empty, 
                        z => z.Group(DataGrouping.Max))
                }), x => x.SubqueryLink("DocId").SetOperation(FilterOperation.OuterJoin))
            .InnerView<DocBySpecType>("ST", x => x.SubqueryLink("DocId"))
            .Column("DocId", x => x.Field<long?>("D", string.Empty, 
                z => z.Key(), z => z.Default(DefaultType.AutoIncrement)))
            .Column("DocStateId", x => x.Field<long>("S", string.Empty))
            .Column("FromCustomerId", x => x.Field<long>("F", "customer_id"))
            .Column("ToCustomerId", x => x.Field<long>("T", "customer_id"))
            .Column("CatalogueId", x => x.Field<long?>("U", string.Empty))
            .ReadOnlyProperty(x => x.DocStateCode, x => x.Field<string>("S", "code"))
            .ReadOnlyProperty(x => x.Generation, 
                x => x.Field<long>("D", string.Empty, 
                    z => z.ConcurrencyToken(), z => z.Default(DefaultType.AutoIncrement)))
            .ReadOnlyProperty(x => x.Changed, 
                x => x.Field<DateTime>("D", string.Empty, 
                    z => z.Default(DefaultType.CurrentDateTime, DefaultFeature.UseOnUpdate)))
            .ReadOnlyProperty(x => x.Author, 
                x => x.Field<string>("D", string.Empty, 
                    z => z.Default(DefaultType.UserName, DefaultFeature.UseOnUpdate)))
            .ReadOnlyProperty(x => x.DocAmount, 
                x => x.Expr<decimal?>(null, DataExpressionType.SubQuery, 
                    "A", DbType.Decimal, z => z.Size(17, 5)))
            .ReadOnlyProperty(x => x.DocLastChange, x => x.Ref<DateTime?>("H", "HistoryDate"))
            .Map(x => new Invoice {
                DocState = x.Link<string, DocState>("S", "name",
                    z => z.LinkProperty<DocState>(y => y.Name),
                    z => z.LinkProperty<DocState>(y => y.Code, y => y.DocStateCode),
                    z => z.LinkProperty<DocState>((Invoice y) => y.GetProperty<long>("DocStateId"))),
                DocCatalog = x.Link<string, Catalogue>("U", "name", 
                    z => z.LinkProperty<Catalogue>(y => y.Name),
                    z => z.LinkProperty<Catalogue>((Invoice y) => y.GetProperty<long>("CatalogueId"))),
                DocNumb = x.Field<string>("N", "numb"),
                DocDate = x.Field<DateTime?>("D", string.Empty, 
                    z => z.Default(DefaultType.CurrentDate)),
                Scan = x.Lob("SC", z => z.OuterFlag()),
                Source = x.Xml("SR", z => z.OuterFlag()),
                CustomerFrom = x.Link<string, Customer>("F", "name", 
                    z => z.LinkProperty<Customer>(y => y.Name),
                    z => z.LinkProperty<Customer>(y => y.GetProperty<long>("CustomerId"), 
                        y => y.GetProperty<long>("FromCustomerId"))),
                CustomerTo = x.Link<string, Customer>("T", "name", 
                    z => z.LinkProperty<Customer>(y => y.Name),
                    z => z.LinkProperty<Customer>(y => y.GetProperty<long>("CustomerId"), 
                        y => y.GetProperty<long>("ToCustomerId"))),
                DeliveryType = x.Field<DeliveryTypeEnum>("DD", string.Empty),
                DeliveryDate = x.Field<DateTime?>("DD", string.Empty, 
                    z => z.Default(DefaultType.CurrentDate))
            }, x => x.ExternalLink<InvoiceSpec>("DocId"))
            .SetBaseTable("D").SetLogicAssembly("XDataObjectTest")
    );
}
...    
public partial class Invoice
{
    public string DocStateCode { get 
        { return this.GetProperty(x => x.DocStateCode); } 
    }
    public Link<string, DocState> DocState { get; set; }
    public Link<string, Catalogue> DocCatalog { get; set; }
    public string DocNumb { get; set; }
    public DateTime? DocDate { get; set; }
    public long Generation { get { return this.GetProperty(x => x.Generation); } }
    public DateTime Changed { get { return this.GetProperty(x => x.Changed); } }
    public string Author { get { return this.GetProperty(x => x.Author); } }
    public decimal? DocAmount { get { return this.GetProperty(x => x.DocAmount); } }
    public DateTime? DocLastChange { get 
        { return this.GetProperty(x => x.DocLastChange); } 
    }
    public Lob Scan { get; set; }
    public Xml Source { get; set; }
    public Link<string, Customer> CustomerFrom { get; set; }
    public Link<string, Customer> CustomerTo { get; set; }
    public DeliveryTypeEnum DeliveryType { get; set; }
    public DateTime? DeliveryDate { get; set; }

    public IRepository<InvoiceSpec> Spec { get 
        { return this.GetRepository().GetChild<InvoiceSpec>(); } 
    }
}

Dynamic mapping provide more representable mechanics to define mapping because of no attribute parameters limitation. Subqueries and inner views can be described in place using dynamic query mechanics. SQL expressions can be described in LINQ style in place. This abilities enreach representability of dynamic mapping.

Dynamic mapping allows split data object to partial class definitions with basic class definition and mapping.

Dynamic mapping initialization

Dynamic mapping is described as private static field of type Expression<CustomMapping<T>> where T - data object type. The value of this property is assigned by call of XDataMapping.CustomMapping<T> method returning IRepositoryStructure<T>. Interface IRepositoryStructure<T> has a number of methods to describe all of mapping entities analog to static mapping attributes.

The XDataMapping.UseMapping<T> method can be used to inherit the mapping description of the data object. The depth of the inheritance hierarchy of the mapping description is not limited. There are several overloads of this method:

UseMapping<T, TParent>() - points to type TParent as the base mapping description class. The TParent class must implement the ISqlObject interface but does not have to be associated with the T class.

UseMapping<T>() - the same as the previous method, only the base class in the usual class inheritance hierarchy acts as the base class in the mapping description hierarchy.

UseMapping<T, TParent>(params Expression<Func<IExternalLinkDefinitionAdapter<T>, IExternalLinkDefinition>>[] externalLinks) - allows you to fully inherit the description of mapping and supplement the description with external links. This method of inheriting the mapping description imposes a limitation - the base class must be the ancestor of the T class.

UseMapping<T>(params Expression<Func<IExternalLinkDefinitionAdapter<T>, IExternalLinkDefinition>>[] externalLinks) - the same as the previous method, only the base class in the usual class inheritance hierarchy acts as the base class in the mapping description hierarchy.

Warning

When inheriting mapping descriptions, a mixture of the static and dynamic description methods within the a single mapping inheritance hierarchy is not allowed!

Mapping parameters

To set parameters of dynamically mapped classes used IRepositoryDescription<T> interface methods:

SetBaseTable - to set base table, (optional, by default used virtual table with the empty alias)
SetContext - database context (optional, by default context will by assigned in runtime)
SetFlags - data source flags (optional, by default None)
SetLogicAssemblies - to set data processing logic classes assemblies FullName for this repository (see Using three tier architecture and IDataLogic<T>)

To get reference to IRepositoryDescription<T> interface is required to define mapping of repository read/write properties using IRepositoryStructure<T> interface method Map Example:

...
.Map(x => new Invoice {
    DocState = x.Link<string, DocState>("S", "name",
        z => z.LinkProperty<DocState>(y => y.Name),
        z => z.LinkProperty<DocState>(y => y.Code, 
            y => y.DocStateCode),
        z => z.LinkProperty<DocState>(
            (Invoice y) => y.GetProperty<long>("DocStateId"))),
    DocCatalog = x.Link<string, Catalogue>("U", "name", 
        z => z.LinkProperty(y => y.Name),
        z => z.LinkProperty<Catalogue>(
            (Invoice y) => y.GetProperty<long>("CatalogueId"))),
    DocNumb = x.Field<string>("N", "numb"),
    DocDate = x.Field<DateTime?>("D", string.Empty, 
        z => z.Default(DefaultType.CurrentDate)),
    Scan = x.Lob("SC", z => z.OuterFlag()),
    Source = x.Xml("SR", z => z.OuterFlag()),
    CustomerFrom = x.Link<string, Customer>("F", "name", 
        z => z.LinkProperty(y => y.Name),
        z => z.LinkProperty<Customer>(y => y.GetProperty<long>("CustomerId"), 
            y => y.GetProperty<long>("FromCustomerId"))),
    CustomerTo = x.Link<string, Customer>("T", "name", 
        z => z.LinkProperty(y => y.Name),
        z => z.LinkProperty<Customer>(y => y.GetProperty<long>("CustomerId"), 
            y => y.GetProperty<long>("ToCustomerId"))),
    DeliveryType = x.Field("DD", string.Empty),
    DeliveryDate = x.Field<DateTime?>("DD", string.Empty, 
        z => z.Default(DefaultType.CurrentDate))
}, x => x.ExternalLink("DocId"))
.SetBaseTable("D").SetLogicAssembly("XDataObjectTest")
...

More detailed repository properties map description see below.

Tables

All tables of data object mapping described by IRepositoryStructure<T>.DataTable method. Tables included into updatable tables hierarchy excluding base table must be defined using special overload allows to set parent table alias.

...
.DataTable("T_DOC", "D", 
    x => x.DictFilter("T_DOC_TYPE", "doc_type_id", "code", "INVOICE"))
.DataTable("T_DOC_DOC_STATE", "DS", "D", x => x.Link("D", "doc_id"))
...

Same way is possible to define mapping to view, but all properties must to mapped as read only properties or hidden fields.

Important

Filters and links between tables is defined using filters parameter of IRepositoryStructure<T>.DataTable method or it's overload. Detailed filter definition described below.

Subqueries

Subqueries can be used for a:

filtration using [not] exists(), [not] in (select) (see below)
inner view
subquery expression as single property source (see below)

Subquery deep level is not limited.

In dynamic mapping of data object subqueries can be defined using one of Subquery or InnerView methods overload of IRepositoryStructure<T> interface to define subqueries or inner views respectively.

While using generic overloads of Subquery or InnerView, parameter IQueryDescription<TDobj> sub can be used with in place dynamic query notation.

Non generic overloads of Subquery or InnerView must provide ISqlType or IDataObject marked class type as parameter Type dobjType to reuse of existing mapping. This class can be mapped using static or dynamic style,
no matter.

...
.Subquery("A", typeof(DocSpecAmounts), "Amount", DataGrouping.Sum, 
    x => x.SubqueryLink("DocId"))
    .Subquery<CatalogueTree>("UT", x => x.CatalogueId, DataGrouping.None)
    .InnerView("H", 
        XDataMapping.GetStructure("H", DataStructureFlag.Grouping)
        .DataTable("T_DOC_HISTORY", "H")
        .Select(x => new {
            DocId = x.Field<long?>("H", string.Empty, 
                z => z.Group(DataGrouping.None)),
            HistoryDate = x.Field<DateTime?>("H", string.Empty, 
                z => z.Group(DataGrouping.Max))
        }), 
        x => x.SubqueryLink("DocId").SetOperation(FilterOperation.OuterJoin))
    .InnerView<DocBySpecType>("ST", x => x.SubqueryLink("DocId"))
...

Filters

Filters can be defined as:

Constant filters
SQL expression filters
Dictionary filters
Range filters
Subquery filters
Inner links

Each of them described by corresponding LINQ expression define their parameters depending on the context of their use and the type of the filter itself (see below). Filter descriptions are provided to filters parameter value of DataTable, Subquery, InnerView and Procedure methods of IRepositoryStructure<T> interface, or WithRecursive method of IQueryWithAdapter<TRoot>. Each of expression has described one filter. Type of filter is declared by one of fabric method of interface inside lambda expression with one of followed filter adapter:

IInnerFilterAdapter - filter adapter to define filters and links inside plain query (see DataTable, Procedure),
ISubqueryLinkAdapter, ISubqueryLinkAdapter<TDObj> and ISubqueryLinkAdapter<T,TDObj> - used in various overloads of subquery definition (see Subquery, InnerView and WithRecursive) to declare links with subqueries.

Specific parameters for each type of filter is described in filter fabric methods described below in specific topics. And some optional modificators can be defined using continue LINQ calls with extension methods:

SetOperation - to set filter operation
AsPrimary - to set optional filter flag
SetCombination - to set filter combination

Each of filter type is defined by own special method of corresponding filter adapter.

Filter operation is defined by enum FilterOperation:

Equal - equal
NotEqual - not equal
LessThan - less then
GreaterThan - greater then
LessThanOrEqual - less or equal
GreaterThanOrEqual - greater or equal
In - field value in range or subquery
NotIn - field value is not in range or subquery
Exists - subquery returns one or more rows
NotExists - subquery returns no rows
Like - field value correlated with find expression
NotLike - field value not correlated with find expression
OuterJoin - left outer join link between query sources (tables or inner views)
Contains - field value correlated with full-text search
NotContains - field value not correlated with full-text search

Filter group of filter can be assigned to it declaration by SetCombination extension of IFilterDescription interface.

...
, x => x.ConstFilter("FilterByCathalogue", "catalogue_id", 
    FilterConstantType.Const, null, FilterNullable.NullsNotAllowed)
    .SetCombination("root")
...

Constant filters

Constant filters allows to restrict selection of data by some constant value. This filter defined by one of ConstFilter method overloads of IInnerFilterAdapter interface.

Constant type list restricted by enum FilterConstantType:

Const - constant with value set by ConstantValue.
Variable - value of variable with name set by ConstantValue.
CurrentDate - current date.
CurrentDateTime - current date and time.
CurrentDateTimeUTC - current date and time (UTC).

...
, x => x.ConstFilter("FilterByCathalogue", "catalogue_id", 
    FilterConstantType.Const, null, FilterNullable.NullsNotAllowed)
    .SetCombination("root")
...

SQL expression filters

To define filter over SQL expression used one of ExprFilter method overloads of IInnerFilterAdapter interface. There are two overloads of ExprFilter method:

IFilterDescription ExprFilter(string field, string exprText) - to use with plain SQL expressions or subquery alias
IFilterDescription ExprFilter<T>(string field, Expression<Func<IQueryStructureAdapter, T>> expression) - to use with SQL expressions as LINQ style expressions

Depending style of SQL expression description that overloads can be used with:

subquery alias,

, ExprFilter("discount" /* field name */, 
    "D" /* ExpressionText: subquery alias */)
    .SetOperation(FilterOperation.In)

dynamic definition of SQL expression,

, ExprFilter("discount" /* field name */, 
    /* expression: SQL expression LINQ style definition */
    x => x.Case<Product, int>( 
        z => z.Field<bool>("is_vip"), z => 0, 1.SetExpression(z => 10)), 
    Operation: FilterOperation.NotEqual /* filter operation */)]

plain SQL expression text.

, ExprFilter("discount" /* field name */, 
    /* ExpressionText: plain SQL expression */
    "case P.is_vip when 1 then 10 else 0 end")
    .SetOperation(FilterOperation.NotEqual)

Important

If possible do not use plain SQL expressions. It possibly use specific SQL dialect features not supported by other DBMS or providers.

Range filters

To define filter checks field value over array of values used RangeFilter methods of IInnerFilterAdapter interface.

...
, RangeFilter("discount", 1, 5, 10)
    .SetOperation(FilterOperation.In)
...

Dictionary filters

Dictionaries surrogate keys may varied over one instance to another, and names can be changed by users. Sometimes business logic need to operate with the concrete value of dictionary. Because of this it's recommended to add into dictionary DB structure a special unique text field - mnemonic code.

XData supports using mnemonic code when define filters on dictionary value - dictionary filters. They defined by one of DictFilter overloads of IInnerFilterAdapter interface.

Important

This filter can be used when dictionary has simple (one field only) identity field and mnemonic code field. But it's a very common case.

...
, x => x.DictFilter("T_DOC_CUST_TYPE", "doc_cust_type_id", "code", "TO")
...

Inner links

To define links between data sources inside query used Link overloads of IInnerFilterAdapter interface.

...
, x => x.Link("DS", "doc_state_id")
...

Tip

Inner links can be used to define optional filters. To specify role in optional filter chain need to call extension method AsPrimary as continuation of Link method call.

Subquery filters

To define filters checks field value is correspond subquery results used SubqueryFilter method of IInnerFilterAdapter.

...
.DataTable("T_DOC_CATALOGUE", "DC", "D", x => x.Link("D", "doc_id"), 
    x => x.SubqueryFilter("catalogue_id", "UT")
    .SetOperation(FilterOperation.In))
.Subquery<CatalogueTree>("UT", x => x.CatalogueId, DataGrouping.None)                   
...

Tip

Subquery filters can be used to define optional filters. To specify role in optional filter chain need to call extension method AsPrimary as continuation of Link method call.

Subquery links

Links with subqueries is used to link not table fields but properties of base class and subquery mapped class including hidden properties. To link with subquery or inner view used one of SubqueryLink method overload of ISubqueryLinkAdapter, ISubqueryLinkAdapter<TDObj> or ISubqueryLinkAdapter<T,TDObj> depending by outer method signature and subquery declaration specific.

...
.InnerView<DocBySpecType>("ST", x => x.SubqueryLink("DocId"))
...

Tip

Subquery links can be used to define optional filters. To specify role in optional filter chain need to call extension method AsPrimary as continuation of Link method call.

Properties

Properties dynamic mapping is performed as method call of IRepositoryStructure<T> interface:

Column method call for each of hidden properties mapping
ReadOnlyProperty method call for each of read only properties mapping
Map method call for declare all of read/write properties mapping

...
.Column("DocId", x => x.Field<long?>("D", string.Empty, 
    z => z.Key(), z => z.Default(DefaultType.AutoIncrement)))
...
.ReadOnlyProperty(x => x.Changed, 
    x => x.Field<DateTime>("D", string.Empty, 
        z => z.Default(DefaultType.CurrentDateTime, DefaultFeature.UseOnUpdate)))
...

Parameter mapper of Map method allows to define mapping rule for multiple read/write properties using lambda expression with IQueryStructureAdapter interface as parameter.

...
.Map(x => new Invoice {
    DocState = x.Link<string, DocState>("S", "name",
        z => z.LinkProperty<DocState>(y => y.Name),
        z => z.LinkProperty<DocState>(y => y.Code, y => y.DocStateCode),
        z => z.LinkProperty<DocState>(
            (Invoice y) => y.GetProperty<long>("DocStateId"))),
    DocCatalog = x.Link<string, Catalogue>("U", "name", 
        z => z.LinkProperty<Catalogue>(y => y.Name),
        z => z.LinkProperty<Catalogue>(
            (Invoice y) => y.GetProperty<long>("CatalogueId"))),
    DocNumb = x.Field<string>("N", "numb"),
    DocDate = x.Field<DateTime?>("D", string.Empty, 
        z => z.Default(DefaultType.CurrentDate)),
    Scan = x.Lob("SC", z => z.OuterFlag()),
    Source = x.Xml("SR", z => z.OuterFlag()),
    CustomerFrom = x.Link<string, Customer>("F", "name", 
        z => z.LinkProperty<Customer>(y => y.Name),
        z => z.LinkProperty<Customer>(y => y.GetProperty<long>("CustomerId"), 
            y => y.GetProperty<long>("FromCustomerId"))),
    CustomerTo = x.Link<string, Customer>("T", "name", 
        z => z.LinkProperty<Customer>(y => y.Name),
        z => z.LinkProperty<Customer>(y => y.GetProperty<long>("CustomerId"), 
            y => y.GetProperty<long>("ToCustomerId"))),
    DeliveryType = x.Field<DeliveryTypeEnum>("DD", string.Empty),
    DeliveryDate = x.Field<DateTime?>("DD", string.Empty, 
        z => z.Default(DefaultType.CurrentDate))
}, x => x.ExternalLink<InvoiceSpec>("DocId"))
...

Interfaces IRepositoryStructureAdapter<T> and IQueryStructureAdapter as successor has some overloads of methods to define mapping:

Field<TVal> - for mapping property to data source field
Expr<TVal> - for mapping property to SQL expression (see detailed description below)
Ref<TVal> - for mapping property to inner view property (see detailed description below)

IRepositoryStructureAdapter<T> also has a special methods to map Lob, Xml and Link<TVal,TSource>

Lob - for mapping Lob property
Xml - for mapping Xml property
Link<TVal,TSource> - for mapping link property (see detailed description below)

These methods of single property mapping has parameter attributes to define additional mapping properties using interface IPropertyMappingAdapter:

Key - primary unique key property flag, possible to mark multiple properties or hidden properties as key properties
Hidden - flag, the field should not be included in the SELECT expression, but can be used in filters and/or links between data sources (tables, subqueries...)
Group - property grouping parameters (see detailed description below)

And using interface IRepositoryPropertyMappingAdapter<T, TValue>:

ConcurrencyToken - concurrency token flag for property
Default - default value assign rules for property
OuterFlag - outer join mandatory flag, values of properties marked this flag is analyzed during making decision of operations over tables in optional references

Important

Required to set at least one property of mapped class as primary unique key. Subqueries and inner views can has no key properties.

Interface IQueryStructureAdapter<T> is used in dynamic queries only. Interfaces IRepositoryStructureAdapter and IRepositoryStructureAdapter<T> is used in dynamic repository mapping only. Interface IPropertyMappingAdapter is used as in dynamic queries as in dynamic repository mapping. Interface IRepositoryPropertyMappingAdapter is inherited from [IPropertyMappingAdapter] and used in dynamic repository mapping only.

SQL expression property

Read only property can be mapped to SQL expression. Dynamic mapping of SQL expression is defined by one of IRepositoryStructureAdapter interface Expr method overload:

Expr<TValue>(string fieldName, Expression<Func<IQueryStructureAdapter, TValue>> expr, DbType dbType, params Expression<Func<IRepositoryExpressionMappingAdapter, IStructureFlag>>[] attributes)
Expr<TValue>(string fieldName, DataExpressionType type, string exprText, DbType dbType, params Expression<Func<IRepositoryExpressionMappingAdapter, IStructureFlag>>[] attributes) - used to define SQL expression DataExpressionType.SubQuery or DataExpressionType.PlainSql

When first overload used, SQL expression is defined by expr parameter value as lambda function with IQueryStructureAdapter interface extension methods inside:

Case<T, TResult>(this IQueryStructureAdapter adapter, Expression<Func<IQueryStructureAdapter, T>> expression, Expression<Func<IQueryStructureAdapter, TResult>> elseValue, params TaggedExpression<T, TResult>[] switchValues) - case when ... then ... else ... end SQL construction
DateDiff<T>(this IQueryStructureAdapter adapter, Expression<Func<IQueryStructureAdapter, DateTime>> finalDate, Expression<Func<IQueryStructureAdapter, DateTime>> startDate, DateDiffKind kind) - date difference
Exists(this IQueryStructureAdapter adapter, string subQueryAlias) - exists(select ...) SQL construction
HasFlag<T>(this IQueryStructureAdapter adapter, Expression<Func<IQueryStructureAdapter, T>> expression, Expression<Func<IQueryStructureAdapter, T>> flagValue, Expression<Func<IQueryStructureAdapter, T>> trueValue, Expression<Func<IQueryStructureAdapter, T>> falseValue) - bitwise mask checking for value
SqlExpr<TResult>(this IQueryStructureAdapter adapter, string format, params Expression<Func<IQueryStructureAdapter, object>>[] expressions) - mixing plain SQL expressions with LINQ style defined expression subparts, parameter format is the string.Format mask
SqlFn<TResult>(this IQueryStructureAdapter adapter, string name, params Expression<Func<IQueryStructureAdapter, object>>[] parameters) - SQL function call expression
SqlParam<TResult>(this IQueryStructureAdapter adapter, string binding, DbType type, params Expression<Func<IProcedureParameterAdapter, IProcedureParameterFlag>>[] attributes) - SQL function parameter value expression

Mapping parameters of SQL expressions is defined by IRepositoryExpressionMappingAdapter interface methods:

NativeSqlType(string nativeSqlType) - set native SQL type name of expression result, used when expression result type has specific SQL type
Size(int size) - set number or varchar length of SQL expression result type
Size(int size, int scale) - set length and scale for floating point SQL data types
subquery,

...
.ReadOnlyProperty(x => x.DocAmount, 
    x => x.Expr<decimal?>(null, DataExpressionType.SubQuery, 
        "A", DbType.Decimal, z => z.Size(17, 5)))
...

LINQ expression,

...
.ReadOnlyProperty(x => x.AllowedDiscount, 
    x => x.Expr<int>(null, y => y.Case<Product, int>( 
        z => z.Field<bool>("is_vip"), z => 0, 1.SetExpression(z => 10)), 
        DbType.Decimal, z => z.Size(17, 5)))
...

plain text SQL expression.

...
.ReadOnlyProperty(x => x.AllowedDiscount, 
    x => x.Expr<int>(null, DataExpressionType.PlainSql, 
        "case P.is_vip when 1 then 10 else 0 end",
        DbType.Decimal, z => z.Size(17, 5)))
...

Important

If possible do not use plain SQL expressions. It possibly use specific SQL dialect features not supported by other DBMS or providers.

Property default value

To set default value of property with dynamic mapping, use one of IRepositoryPropertyMappingAdapter<T, TValue> interface Default method overload:

Default(DefaultType source) - use with DefaultType.AutoIncrement, DefaultType.CurrentDate, DefaultType.CurrentDateTime, DefaultType.CurrentDateTimeUtc, DefaultType.NewGuid, DefaultType.UserName where default value assigned data row on insert only
Default(DefaultType source, DefaultFeature features) - use with DefaultType.AutoIncrement, DefaultType.CurrentDate, DefaultType.CurrentDateTime, DefaultType.CurrentDateTimeUtc, DefaultType.NewGuid, DefaultType.UserName where default value assigned data row on insert or update
Default(DefaultType source, TValue value) - use with DefaultType.Const or DefaultType.Variable where default value assigned data row on insert only
Default(DefaultType source, TValue value, DefaultFeature features) - use with DefaultType.Const or DefaultType.Variable where default value assigned data row on insert or update

...
.ReadOnlyProperty(x => x.Changed, 
    x => x.Field<DateTime>("D", string.Empty, 
        z => z.Default(DefaultType.CurrentDateTime, DefaultFeature.UseOnUpdate)))
...

Property grouping parameters

Property grouping parameters used when data object mapped to grouped query to specify property role in GROUP BY expression. Use one of IPropertyMappingAdapter interface Group method overload:

Group(int groupOrder) - when property mapped to field query grouped by with N-th order
Group(DataGrouping grouping) - when property mapped to aggregation function over field

...
HistoryDate = x.Field<DateTime?>("H", string.Empty, 
    z => z.Group(DataGrouping.Max))
...

Hidden properties

Hidden properties dynamically defined using Column method call of IRepositoryStructure<T> interface.

Hidden property can be mapped to:

data source field - Field<TVal>
SQL expression (see detailed description above) - Expr<TVal>
inner view property (see detailed description below) - Ref<TVal>

...
.Column("DocId", x => x.Field<long?>("D", string.Empty, 
    z => z.Key(), z => z.Default(DefaultType.AutoIncrement)))
...

Inner view reference

To map property to field from inner view used one of IRepositoryStructureAdapter interface Ref method overload:

.ReadOnlyProperty(x => x.DocLastChange, x => x.Ref<DateTime?>("H", "HistoryDate"))

Links

To dynamically define link property used one of IRepositoryStructureAdapter<T> interface Link method overload:

Link<TValue, TSource> Link<TValue, TSource>(string sourceAlias, params Expression<Func<ILinkPropertyMappingAdapter<T>, IStructureFlag>>[] attributes) - used when source property is correlated with property name with default name mapping rule (hungarian_notation in DB and CamelCaseNotation property name in code)
Link<TValue, TSource> Link<TValue, TSource>(string sourceAlias, string fieldName, params Expression<Func<ILinkPropertyMappingAdapter<T>, IStructureFlag>>[] attributes)

Parameter attributes of method Link value used to define link properties pairs with one of ILinkPropertyMappingAdapter<T> interface LinkProperties method overload call:

LinkProperty() - used property of source object to associate with link property defined by Link method
LinkProperty<TSource>(Expression<Func<TSource, object>> source) - used source as source property and equal named property from target object
LinkProperty<TSource>(Expression<Func<TSource, object>> source, Expression<Func<T, object>> property) - specifying both properties

...
DocState = x.Link<string, DocState>("S", "name",
    z => z.LinkProperty<DocState>(y => y.Name),
    z => z.LinkProperty<DocState>(y => y.Code, y => y.DocStateCode)
    z => z.LinkProperty<DocState>(
        (Invoice y) => y.GetProperty<long>("DocStateId"))),
...

Tree

To dynamically map tree organized data structure used special method With of RepositoryStructure<T> interface to describe tree root query and tree childs. Both of them use subquery mappings (see example below) to set subquery structure for tree query construction.

Important

Data source flags of tree organized data sources must contain DataStructureFlag.ReadOnly and DataStructureFlag.Tree!

private static Expression<CustomMapping<CatalogueTree>> _unused = (
    () => XDataMapping.CustomMapping<CatalogueTree>()
        .With("CAT", XDataMapping.GetStructure("R")
            .DataTable("T_CATALOGUE", "R",  
                x => x.Combination("root", Combination.Or),
                x => x.ConstFilter("FilterByCathalogue", "catalogue_id", 
                        FilterConstantType.Const, null, 
                        FilterNullable.NullsNotAllowed).SetCombination("root"),
                x => x.ConstFilter("FilterByRoot", "parent_id",
                        FilterConstantType.Const, null, 
                        FilterNullable.NullsCompared)
                    .SetCombination("root"))
            .Select(x => new {
                CatalogueId = x.Field<long>("R", string.Empty, z => z.Key()),
                Code = x.Field<string>("R", string.Empty),
                Name = x.Field<string>("R", string.Empty),
                ParentId = x.Field<long?>("R", string.Empty),
            }), x => x.Properties(z => z.CatalogueId, 
                z => z.Code, z => z.Name, z => z.ParentId),
            x => x.WithRecursive("S", XDataMapping
                .GetStructure("S")
                .DataTable("T_CATALOGUE", "S")
                .Select(y => new {
                    CatalogueId = y.Field<long>("S", string.Empty, z => z.Key()),
                    Code = y.Field<string>("S", string.Empty),
                    Name = y.Field<string>("S", string.Empty),
                    ParentId = y.Field<long?>("S", string.Empty)
                }), 
                z => z.SubqueryLink(y => y.ParentId, y => y.CatalogueId)))
        .ReadOnlyProperty(x => x.CatalogueId, 
            x => x.Field<long>("CAT", string.Empty, z => z.Key()))
        .ReadOnlyProperty(x => x.Code, 
            x => x.Field<string>("CAT", string.Empty))
        .ReadOnlyProperty(x => x.Name, 
            x => x.Field<string>("CAT", string.Empty))
        .ReadOnlyProperty(x => x.ParentId, 
            x => x.Field<long?>("CAT", string.Empty))
        .Map()
        .SetFlags(DataStructureFlag.ReadOnly | DataStructureFlag.Tree)
    );

External link

External link can be dynamically defined using expression of type Expression as part of call Map method of IRepositoryStructure interface.

Expression<IExternalLinkDefinition> can be obtained using multiple overloads of ExternalLink method of ExternalLinkDefinitionAdapter<T>:

IExternalLinkAdapter<T, TChild> used to specify properties of external link:

Filter - used to specify external link runtime filter,
FilterCombination - used to set filter group of filter,
DirectLink - used to set direct link flag, see tree master-detail specifics for details,
MasterRefresh - used to set refresh master repository current row data on submit details changes flag (useful when master has calculated properties over detail repository data),
Nullable - filter null processing behavior,
Operation - link filter operation (see Filters),
PrimaryFilter - used to specify external link is optional filter,
Property - link property in master repository (used when property is hidden property),
Property<TChildLink> - link property in master repository (used when property is actual property),
TreeFilter - used to set external link filter's tree filter flag

// when external link used hidden property
..., x => x.ExternalLink<InvoiceSpec>("DocId"))
// when external link used read-only or regular property
..., x => x.ExternalLink<InvoiceSpec>(z => z.DocId))

Variables

The use of variables is described here.

To describe the variables, special overloads of Subquery and InnerView methods are used. Variables are described in the variables parameter.

.Subquery<DocPropertyValue>("PH", x => x.GetProperty("ParamValue"), DataGrouping.None, 
    new[]{"ParamCode".SetVar("AUTHOR_PHONE")}, /* variables */
    x => x.SubqueryLink("DocId"))

SQL procedures & functions

Common information about mapping to SQL procedures and functions described in common mapping rules description and here we just represent dynamic mapping rules for various procedure types.

Warning

SQLite not supported SQL procedures & functions

Warning

PostgreSql functions has restrictions:

returning refcursor or set of refcursor requires transaction!
parameters use lower case names ONLY!
result set names (refcursor names) use lower case names ONLY!

SQL procedure as query

To define procedure as data source of mapping used Procedure method call of IRepositoryStructure<T> interface.

SQL procedure parameter

To define SQL procedure parameter used Parameter method call of IRepositoryStructure<T> interface.

SQL procedure result set

To describe result set of SQL procedure used Return method call of IRepositoryStructure<T> interface.

* - when result set is not mapped to procedure parameter set result set name to string.Empty

SQL procedure result set as query sample

MS SQL Server

CREATE PROCEDURE [dbo].[TestProcedure]
(   
    @param1 int, 
    @param2 varchar(20)
)
AS
    SELECT @param1 as Id, @param2 as Name

Oracle

procedure TestProcedure(p_Id int, p_Name varchar2, p_Out out SYS_REFCURSOR)
as
begin
    open p_Out for select p_Id as Id, p_Name as Name from dual;
end TestProcedure;

PostgreSQL

CREATE OR REPLACE FUNCTION public.TestProcedure (p_id integer, p_name varchar, refcursor)
RETURNS refcursor AS
$$
begin
    open $3 for select p_id as Id, p_name as Name;
    return $3;
end
$$ LANGUAGE plpgsql;

MySQL

create procedure TestProcedure(p_Id int, p_Name varchar(50))
begin
    select p_Id as Id, p_Name as Name;
end

Dynamic query

Expression<Func<IQueryStructureAdapter, PostgreSqlClassifier>> convertor =
    x => new PostgreSqlClassifier { Id = x.Field<int>(z => z.Key()), Name = x.Field<string>() };
var res = XDataMapping.GetStructure("T")
    .Procedure("T", "dbo.TestProcedure", ProcedureType.Procedure)
    .Parameter<int>("T", 1, "param1", DbType.Int32)
    .Parameter<string>("T", 2, "param2", DbType.String)
    .Return("T", string.Empty, 1, convertor)
    .Call<Classifier>(DataScope, string.Empty, 
        "param1".SetVar(1), "param2".SetVar("Test"));
foreach (var classifier in res)
    Console.WriteLine("{0} - \"{1}\"", classifier.Id, classifier.Name);

Mapping

...
.Procedure("T", "dbo.TestProcedure", ProcedureType.Procedure)
.Parameter<int>("T", 1, "param1", DbType.Int32)
.Parameter<string>("T", 2, "param2", DbType.String)
.Return("T")
.Map(x => new TestProcedure
{
    Id = x.Field<int>("T", string.Empty, z => z.Key()), 
    Name = x.Field<string>("T", string.Empty)
})
.SetBaseTable("T")
...

SQL procedure with multiple result sets sample

MS SQL Server

CREATE PROCEDURE [dbo].[TestProcedure2]
(   
    @param1 int, 
    @param2 varchar(20),
    @param3 int, 
    @param4 varchar(20)
)
AS
BEGIN
    SELECT @param1 as Id, @param2 as Name
    SELECT @param3 as Id, @param4 as Name
END

Oracle

procedure TestProcedure2(p_Id int, p_Name varchar2, p_Out out SYS_REFCURSOR, p_Out2 out SYS_REFCURSOR)
as
begin
    open p_Out for select p_Id as Id, p_Name as Name from dual;
    open p_Out2 for select p_Id as Id, p_Name as Name from dual;
end TestProcedure2;

Tip

Use refcursor parameters names as ResultSet names!

PostgreSQL

CREATE OR REPLACE FUNCTION public.TestProcedure2 (p_id integer, p_name varchar, refcursor, refcursor)
RETURNS SETOF refcursor AS
$$
begin
    open $3 for select p_id as Id, p_name as Name;
    return NEXT $3;
    open $4 for select p_id as Id, p_name as Name;
    return NEXT $4;
end
$$ LANGUAGE plpgsql;

Warning

Functions witch returns setof refcursor is not supported by NpgSql .Net Standard 2.0 Edition :( XData net4.0 version can call that type functions with the same mapping rules as described here...

Tip

If refcursor parameters have a names it must be used as ResultSet names!

MySQL

create procedure TestProcedure2(p_Id int, p_Name varchar(50))
begin
    select p_Id as Id, p_Name as Name;
    select p_Id as Id, p_Name as Name;
end

Dynamic query

Expression<Func<IQueryStructureAdapter, PostgreSqlClassifier>> convertor =
    x => new PostgreSqlClassifier { Id = x.Field<int>(z => z.Key()), Name = x.Field<string>() };
var res2 = XDataMapping.GetStructure("T")
    .Procedure("T", "dbo.TestProcedure2", ProcedureType.Procedure)
    .Parameter<int>("T", 1, "param1", DbType.Int32)
    .Parameter<string>("T", 2, "param2", DbType.String)
    .Parameter<int>("T", 3, "param3", DbType.Int32)
    .Parameter<string>("T", 4, "param4", DbType.String)
    .Return("T", "Result1", 1, convertor)
    .Return("T", "Result2", 2, convertor)
    .Call<Classifier>(DataScope, "Result1", "param1".SetVar(1), 
        "param2".SetVar("Test"), "param3".SetVar(2), "param4".SetVar("Some"));
foreach (var classifier in res2)
    Console.WriteLine("{0} - \"{1}\"", classifier.Id, classifier.Name);
foreach (var classifier in res2.GetResultSet<Classifier>("Result2"))
    Console.WriteLine("{0} - \"{1}\"", classifier.Id, classifier.Name);

Mapping

...
.Procedure("T", "dbo.TestProcedure2", ProcedureType.Procedure)
.Parameter<int>("T", 1, "param1", DbType.Int32)
.Parameter<string>("T", 2, "param2", DbType.String)
.Parameter<int>("T", 3, "param3", DbType.Int32)
.Parameter<string>("T", 4, "param4", DbType.String)
.Return("T")
.Return("T", "Result2", 2, 
    x => new PostgreSqlClassifier { 
        Id = x.Field<int>(z => z.Key()), 
        Name = x.Field<string>() })
.Map(x => new TestProcedure2
{
    Id = x.Field<int>("T", string.Empty, z => z.Key()), 
    Name = x.Field<string>("T", string.Empty)
})
.SetBaseTable("T")
...

SQL procedure with out parameter sample

MS SQL Server

CREATE PROCEDURE [dbo].[TestProcedure3]     
(   
    @param1 int, 
    @param2 varchar(20),
    @param3 varchar(20) OUTPUT
)
AS
SET @param3 = convert(varchar(20), @param1) + ' - ' + @param2
RETURN

Oracle

procedure TestProcedure3(p_Id int, p_Name varchar2, p_Out out varchar2)
as
begin
    select p_Id || ' - ' || p_Name into p_Out from dual;
end TestProcedure3;

PostgreSQL

CREATE or REPLACE function TestProcedure3(p_id integer, p_name varchar, out p_out varchar) as $$
begin
    p_Out := p_id || ' - ' || p_name;
end
$$ LANGUAGE plpgsql;

MySQL

create procedure TestProcedure3(p_Id int, p_Name varchar(50), out p_Out varchar(100))
begin
    set p_Out = CONCAT(p_Id, ' - ', p_Name);
end

Dynamic query

var res3 = XDataMapping.GetStructure("T")
    .Procedure("T", "dbo.TestProcedure3", ProcedureType.Procedure)
    .Parameter<int>("T", 1, "param1", DbType.Int32)
    .Parameter<string>("T", 2, "param2", DbType.String)
    .Parameter<string>("T", 3, "param3", DbType.String, 
        z => z.Size(20), z => z.Direction(ParameterDirection.Output))
    .Call(DataScope.Layer, 
        "param1".SetVar(1), "param2".SetVar("Test"));
Console.WriteLine(res3.GetParameter<string>("param3"));

Mapping

...
.Procedure("T", "dbo.TestProcedure3", ProcedureType.Procedure)
.Parameter<int>("T", 1, "param1", DbType.Int32)
.Parameter<string>("T", 2, "param2", DbType.String)
.Parameter<string>("T", 3, "param3", DbType.String, 
    z => z.Size(20), z => z.Direction(ParameterDirection.Output))
.Map()
.SetBaseTable("T")
...

SQL procedure with out parameter and result set sample

MS SQL Server

CREATE PROCEDURE [dbo].[TestProcedure4]     
(   
    @param1 int, 
    @param2 varchar(20)
)
AS
SELECT @param1 as Id, @param2 as Name
RETURN(@param1)

Oracle

procedure TestProcedure4(p_Tab in classifier_tab, p_Out out SYS_REFCURSOR)
as
begin
    open p_Out for select Id, Name from table(p_Tab);
end TestProcedure4;

Tip

Use refcursor parameters names as ResultSet names!

PostgreSQL

CREATE OR REPLACE FUNCTION public.TestProcedure4 (p_tab public.classifier_rec [], refcursor)
RETURNS refcursor AS
$$
begin
    open $2 for select Id, Name from unnest(p_tab);
    return $2;
end
$$
LANGUAGE plpgsql;

Tip

If refcursor parameters have a names it must be used as ResultSet names!

MySQL

Not supported

Dynamic query

var res4 = XDataMapping.GetStructure("T")
    .Procedure("T", "dbo.TestProcedure4", ProcedureType.Procedure)
    .Parameter<int>("T", 1, "param1", DbType.Int32)
    .Parameter<string>("T", 2, "param2", DbType.String)
    .Parameter<int>("T", 3, "result", DbType.Int32, 
        z => z.Direction(ParameterDirection.ReturnValue))
    .Return("T", string.Empty, 1, x => new Classifier { 
        Id = x.Field<int>(z => z.Key()), 
        Name = x.Field<string>() })
    .Call<Classifier>(DataScope, string.Empty, 
        "param1".SetVar(1), "param2".SetVar("Test"));
Console.WriteLine(res4.GetParameter<int>("result"));
foreach (var classifier in res4)
    Console.WriteLine("{0} - \"{1}\"", classifier.Id, classifier.Name);

Mapping

...
.Procedure("T", "dbo.TestProcedure4", ProcedureType.Procedure)
.Parameter<int>("T", 1, "param1", DbType.Int32)
.Parameter<string>("T", 2, "param2", DbType.String)
.Parameter<int>("T", 3, "result", DbType.Int32, 
    z => z.Direction(ParameterDirection.ReturnValue))
.Return("T")
.Map(x => new TestProcedure4
{
    Id = x.Field<int>("T", string.Empty, z => z.Key()), 
    Name = x.Field<string>("T", string.Empty)
})
.SetBaseTable("T")

SQL procedure with return value sample

MS SQL Server

CREATE PROCEDURE [dbo].[TestProcedure5]     
(   
    @param1 int, 
    @param2 varchar(20)
)
AS
RETURN(@param1)

Oracle

Not supported

PostgreSQL

Not supported

MySQL

Not supported

Dynamic query

var res5 = XDataMapping.GetStructure("T")
    .Procedure("T", "dbo.TestProcedure5", ProcedureType.Procedure)
    .Parameter<int>("T", 1, "param1", DbType.Int32)
    .Parameter<string>("T", 2, "param2", DbType.String)
    .Parameter<int>("T", 3, "result", DbType.Int32, 
        z => z.Direction(ParameterDirection.ReturnValue))
    .Call<Classifier>(DataScope.Layer, string.Empty, 
        "param1".SetVar(1), "param2".SetVar("Test"));
Console.WriteLine(res5.GetParameter<int>("result"));

Mapping

...
.Procedure("T", "dbo.TestProcedure5", ProcedureType.Procedure)
.Parameter<int>("T", 1, "param1", DbType.Int32)
.Parameter<string>("T", 2, "param2", DbType.String)
.Parameter<int>("T", 3, "result", DbType.Int32, 
    z => z.Direction(ParameterDirection.ReturnValue))
.Map()
.SetBaseTable("T")
...
public int ResultParameter 
        { get { return this.GetParameter<int>("result"); } }

SQL procedure with return value and result set sample

MS SQL Server
MS SQL Server

CREATE PROCEDURE [dbo].[TestProcedure6]     
(   
    @param1 int, 
    @param2 varchar(20)
)
AS
SELECT @param1 as Id, @param2 as Name
RETURN(@param1)

Oracle

Not supported

PostgreSQL

Not supported

MySQL

Not supported

Dynamic query

var res6 = XDataMapping.GetStructure("T")
    .Procedure("T", "dbo.TestProcedure6", ProcedureType.Procedure)
    .Parameter<int>("T", 1, "param1", DbType.Int32)
    .Parameter<string>("T", 2, "param2", DbType.String)
    .Parameter<int>("T", 3, "result", DbType.Int32, 
        z => z.Direction(ParameterDirection.ReturnValue))
    .Return("T", string.Empty, 1, x => new Classifier { 
        Id = x.Field<int>(z => z.Key()), Name = x.Field<string>() })
    .Call<Classifier>(DataScope.Layer, string.Empty, "param1".SetVar(param));
foreach (var classifier in res6)
    Console.WriteLine("{0} - \"{1}\"", classifier.Id, classifier.Name);

Mapping

...
.Procedure("T", "dbo.TestProcedure6", ProcedureType.Procedure)
.Parameter<int>("T", 1, "param1", DbType.Int32)
.Parameter<string>("T", 2, "param2", DbType.String)
.Parameter<int>("T", 3, "result", DbType.Int32, 
    z => z.Direction(ParameterDirection.ReturnValue))
.Return("T")
.Map(x => new TestProcedure6
{
    Id = x.Field<int>("T", string.Empty, z => z.Key()), 
    Name = x.Field<string>("T", string.Empty)
})
.SetBaseTable("T")
...
public int ResultParameter 
        { get { return this.GetParameter(x => x.ResultParameter); } }

SQL procedure with simple array parameter sample

MS SQL Server

Not supported

Oracle

Not supported

PostgreSQL

CREATE or REPLACE function TestFunction6(p_arr INTEGER[]) returns integer as $$
begin
    return sum(s) from unnest(p_arr) s;
end
$$ LANGUAGE plpgsql;

MySQL

Not supported

Dynamic query

var arr = new[] { 1, 2, 3 };
var f6 = XDataMapping.GetStructure("T")
    .Procedure("T", "TestFunction6", ProcedureType.Function)
    .Parameter<int[]>("T", 1, "p_arr", DbType.Int32, z => z.Array())
    .Parameter<int>("T", 2, "result", DbType.Int32, 
        z => z.Direction(ParameterDirection.ReturnValue))
    .Call(DataScope, "p_arr".SetVar(arr));
Console.WriteLine(f6.GetParameter<int>("result"));

Mapping

...
.Procedure("T", "TestFunction6", ProcedureType.Function)
.Parameter<int[]>("T", 1, "p_arr", DbType.Int32, z => z.Array())
.Parameter<int>("T", 2, "result", DbType.Int32, 
    z => z.Direction(ParameterDirection.ReturnValue))
...

SQL procedure with UDT parameter sample

MS SQL Server

Not supported

Tip

MS SQL Server does not support passing single UDT object as a parameter value, but support array of UDT

Oracle

Not supported

Warning

Oracle ODP.Net provider does not support UDT

PostgreSQL

CREATE TYPE public.classifier_rec AS ( Id integer, Name varchar(20) );

CREATE or REPLACE function TestFunction5(p_obj classifier_rec) returns varchar as $$
begin
    return p_obj.Id || ' - ' || p_obj.Name;
end
$$ LANGUAGE plpgsql;

MySQL

Not supported

Dynamic query

/* Type mapped to UDT required to be Xml serializable */
[Serializable]
public class Classifier
{
    [XmlAttribute]
    public int Id { get; set; }
    [XmlAttribute]
    public string Name { get; set; }
}
...
var f5 = XDataMapping.GetStructure("T")
    .Procedure("T", "TestFunction5", ProcedureType.Function)
    .Parameter<Classifier>("T", 1, "p_obj", DbType.Object, 
        z => z.UdtDataType("classifier_rec"))
    .Parameter<string>("T", 3, "result", DbType.String, 
        z => z.Size(20),
        z => z.Direction(ParameterDirection.ReturnValue))
    .Call(DataScope, "p_obj".SetVar(
        new Classifier { Id = 1, Name = "Test" }));
Console.WriteLine(f5.GetParameter<string>("result"));

Mapping

/* Type mapped to UDT required to be Xml serializable */
[Serializable]
public class Classifier
{
    [XmlAttribute]
    public int Id { get; set; }
    [XmlAttribute]
    public string Name { get; set; }
}
...
.Procedure("T", "TestFunction5", ProcedureType.Function)
.Parameter<Classifier>("T", 1, "p_obj", DbType.Object, 
    z => z.UdtDataType("classifier_rec"))
.Parameter<string>("T", 3, "result", DbType.String, 
    z => z.Size(20),
    z => z.Direction(ParameterDirection.ReturnValue))
.Map()
.SetBaseTable("T")
...
public class TestFunction5 : IDataObject
{
    public string Result => this.GetParameter(x => x.Result);
}

SQL procedure with UDT array parameter sample

MS SQL Server

CREATE TYPE [dbo].[Classifier] AS TABLE(
    [Id] [int] NULL,
    [Name] [varchar](20) NULL
)

CREATE PROCEDURE [dbo].[TestProcedure7] (@param1 Classifier READONLY)
AS
SELECT * from @param1

Oracle

Not supported

Warning

Oracle ODP.Net provider does not support UDT

PostgreSQL

CREATE TYPE public.classifier_rec AS ( Id integer, Name varchar(20) );

CREATE or REPLACE function TestProcedure7(p_tab classifier_rec[]) returns setof classifier_rec as $$
    select a.Id, a.Name from unnest(p_tab) a;
$$ LANGUAGE sql;

MySQL

Not supported

Dynamic query

/* Type mapped to UDT required to be Xml serializable */
[Serializable]
public class Classifier
{
    [XmlAttribute]
    public int Id { get; set; }
    [XmlAttribute]
    public string Name { get; set; }
}
...
var param = new[] { new Classifier { Id = 1, Name = "Test" },
    new Classifier { Id = 2, Name = "Some" } };
var f4 = XDataMapping.GetStructure("T")
    .Procedure("T", "dbo.TestProcedure7", ProcedureType.Procedure)
    .Parameter<Classifier[]>("T", 1, "param1", DbType.Object, 
        z => z.UdtDataType(null, "dbo.Classifier"))
    .Return("T")
    .Call(DataScope, string.Empty, "param1".SetVar(param));
foreach (var classifier in res)
    Console.WriteLine("{0} - \"{1}\"", classifier.Id, classifier.Name);

Mapping

/* Type mapped to UDT required to be Xml serializable */
[Serializable]
public class Classifier
{
    [XmlAttribute]
    public int Id { get; set; }
    [XmlAttribute]
    public string Name { get; set; }
}
...
.Procedure("T", "dbo.TestProcedure7", ProcedureType.Procedure)
.Parameter<Classifier[]>("T", 1, "param1", DbType.Object, 
    z => z.UdtDataType(null, "dbo.Classifier"))
.Return("T")
.Map(x => new TestProcedure
{
    Id = x.Field<int>("T", string.Empty, z => z.Key()), 
    Name = x.Field<string>("T", string.Empty)
})
.SetBaseTable("T")

SQL table valued function as query sample

MS SQL Server

CREATE FUNCTION [dbo].[TestFunction] 
(   
    @param1 int, 
    @param2 varchar(20)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT @param1 as Id, @param2 as Name
)

Oracle

CREATE OR REPLACE type classifier_rec is object (Id int, Name varchar2(20));
CREATE OR REPLACE type classifier_tab is table of classifier_rec;

function TestFunction(p_Id int, p_Name varchar2) return classifier_tab pipelined
is
begin
for curr in (select p_Id as Id, p_Name as Name from dual) loop
  pipe row (classifier_rec(curr.Id, curr.Name));
end loop;
end TestFunction;

PostgreSQL

CREATE or REPLACE function TestFunction(p_id integer, p_name varchar) 
returns TABLE (
  Id bigint,
  Name varchar
) as $$
    select p_id as Id, p_name as Name;
$$ LANGUAGE SQL;

... or with the same mapping syntax...

CREATE TYPE public.classifier_rec AS ( Id integer, Name varchar(20) );

CREATE or REPLACE function TestFunction(p_Id integer, p_Name varchar) returns setof classifier_rec as $$
    select p_Id as Id, p_Name as Name;
$$ LANGUAGE SQL;

MySQL

Not supported

Dynamic query

var f3 = XDataMapping.GetStructure()
    .Procedure("T", "dbo.TestFunction", ProcedureType.Function)
    .Parameter<int>("T", 1, "param1", DbType.Int32)
    .Parameter<string>("T", 2, "param2", DbType.String)
    .Return("T")
    .Select(x => new Classifier { 
        Id = x.Field<int>("T", "Id", z => z.Key()), 
        Name = x.Field<string>("T", "Name") })
    .AsQuery(DataScope, "param1".SetVar(param));
foreach (var classifier in f3)
    Console.WriteLine("{0} - \"{1}\"", classifier.Id, classifier.Name);

Dynamic query table function with joined table

var f1Join = XDataMapping.GetStructure()
    .DataTable("T_DOC_TYPE", "D")
    .Procedure("T", "dbo.TestFunction", ProcedureType.Function, 
        z => z.Link("D", "doc_type_id", "Id"))
    .Parameter<int>("T", 1, "param1", DbType.Int32)
    .Parameter<string>("T", 2, "param2", DbType.String)
    .Return("T")
    .Select(x => new {
        Id = x.Field<int>("T", "Id", z => z.Key()),
        Name = x.Field<string>("T", "Name"),
        Code = x.Field<string>("D", string.Empty)
    })
    .AsQuery(DataScope, "param1".SetVar(1), "param2".SetVar("Test"));
foreach (var res in f1Join)
    Console.WriteLine("{0} - \"{1}\" - \"{2}\"", res.Id, res.Name, res.Code);

Mapping

...
.Procedure("T", "dbo.TestFunction", ProcedureType.Function)
.Parameter<int>("T", 1, "param1", DbType.Int32)
.Parameter<string>("T", 2, "param2", DbType.String)
.Return("T")
.Map(x => new TestFunction
{
    Id = x.Field<int>("T", "Id", z => z.Key()), 
    Name = x.Field<string>("T", "Name")
})
.SetBaseTable("T")

Mapping table function with joined table

.DataTable("T_DOC_TYPE", "D")
.Procedure("T", "dbo.TestFunction", ProcedureType.Function, 
    z => z.Link("D", "doc_type_id", "Id"))
.Parameter<int>("T", 1, "param1", DbType.Int32)
.Parameter<string>("T", 2, "param2", DbType.String)
.Return("T")
.Map(x => new TestFunctionJoin
{
    Id = x.Field<int>("T", "Id", z => z.Key()),
    Name = x.Field<string>("T", "Name"),
    Code = x.Field<string>("D", string.Empty)
})
.SetBaseTable("D")

SQL scalar function as property data source sample

MS SQL Server

CREATE FUNCTION [dbo].[TestFunction2]
(   
    @param1 int, 
    @param2 varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
DECLARE @res varchar(20) 
SET @res = convert(varchar(20), @param1) + ' - ' + @param2
RETURN @res
END

Oracle

function TestFunction2(p_Id int, p_Name varchar2) return varchar2
is
begin
  return p_Id || ' - ' || p_Name;
end TestFunction2;

PostgreSQL

CREATE or REPLACE function TestFunction2(p_id integer, p_name varchar) returns varchar as $$
DECLARE
ret varchar;
begin
    select p_id || ' - ' || p_name into ret;
    return ret;
end
$$ LANGUAGE plpgsql;

MySQL

create function TestFunction2(p_Id int, p_Name varchar(20)) returns varchar(100)
begin
    declare res varchar(100);
    select CONCAT(p_Id, ' - ', p_Name) into res;
    return(res);
end

Mapping

var t1 = XDataMapping.GetStructure("T")
    .DataTable("T_DOC_TYPE", "T").Select(x => new
    {
        DocTypeId = x.Field<long>("T", "doc_type_id", z => z.Key()),
        Code = x.Field<string>("T", "code"),
        Name = x.Field<string>("T", "name"),
        Test = x.Expr("test", z => z.SqlFn<string>("dbo.TestFunction2",
            y => y.Field<long>("T", "doc_type_id"), y => y.Field<string>("T", "name")), DbType.String, z => z.Size(20))
    }).AsQuery(DataScope);
foreach (var z in t1)
    Console.WriteLine("{0} - \"{1}\" - \"{2}\" - \"{3}\"", z.DocTypeId, z.Code, z.Name, z.Test);