// OrmDb Net

// Doc: https://ormdb.net/wiki.html

​

// @nuget: System.Data.SqlClient

// @nuget: OrmDb

​

using Odb;

using Odb.Table;

using System;

using System.Linq;

using System.Linq.Expressions;

public class Program

{   

    public static void Main()

    {

        Console.WriteLine("OrmDb v." + Odb.Version.ToString() + "\n");  

        using(var cn = new System.Data.SqlClient.SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) {

            cn.OpenIf();

            // Table Aliases

            var t0 = new Orders().AsQueryable();

            var t1 = new OrderDetails().AsQueryable();

​

            // sql

            var query = Odb.SQL.Select(x => x.From(t0)

                                       .Fields(t0.EmployeeID, x[t0.OrderID].MAX().AS("LastOrderId"))

                                       .GroupBy(t0.EmployeeID));

            // serialize

            Console.WriteLine(cn.GetScript(query) + "\n");

​

            // Executing

            var result = cn.Sql(query).Read();

            FiddleHelper.WriteTable(result.ToDataTable());

        }

    }

        // table definition [Orders]

        public class Orders : Base

        {

            [Column(AutoIncrement=true)]public int? OrderID {get;set;}

            [Column()]public int? CustomerID                {get;set;}

            [Column()]public int? EmployeeID                {get;set;}

            [Column()]public DateTime? OrderDate            {get;set;}

            [Column(DefaultValue = 1)]public int? ShipperID {get;set;}

​

            protected override Expression<TableConfig> Config() =>

                (x) => x.SetPrimaryKey(OrderID)

                        .AddIndex(CustomerID, OrderDate);

        }

        // table definition [OrderDetails]

        public class OrderDetails : Base

        {

            [Column()]public int? OrderDetailID {get;set;}

            [Column()]public int? OrderID       {get;set;}

            [Column()]public int? ProductID     {get;set;}

            [Column()]public int? Quantity      {get;set;}

​

            protected override Expression<TableConfig> Config() =>

                (x) => x.SetPrimaryKey(OrderDetailID);

        }

        // table definition [Products]

        public class Products : Base

        {

            [Column(AutoIncrement=true)]public int? ProductID {get;set;}

            [Column()]public string ProductName             {get;set;}

            [Column()]public int? SupplierID                {get;set;}

            [Column()]public int? CategoryID                {get;set;}

            [Column()]public string Unit                    {get;set;}

            [Column()]public decimal? Price                 {get;set;}

​

            protected override Expression<TableConfig> Config() =>

                (x) => x.SetPrimaryKey(ProductID);

        }

}