APQuery.net

APQuery.net is a lightweight ORM, open source framework. With the objectization of the database, SQL expression, database access layout, business logic process and data entity.

Home page, documentation, and support links: http://leocaan.github.io/APQuery.net/

Some demos will be links: https://github.com/leocaan/APQuery.net-Demo/

This project no longer to develop, I created a new project 'APQuery.net-4x' links: https://github.com/leocaan/APQuery.net-4x/

Getting Started

• Install APQuery.net Library Package from Nuget, search as the key "APQuery".
• Install APQuery.net Add-in from Expanded and updated, Visual Studio Menu -> Tools -> Expanded and updated, search as the key "APQuery".

• Right click contextmenu on the project folder and click Add new .apgen file to create a business.apgen file, this file is the ORM mapping file.

• If you project is WebSite, ensure the file is in the App_Code folder will be auto generated.
• Or if you project is WebApplication, right click context menu on the file and click Generate the .apgen file item will be generated business.apgen.cs in project.

• Edit Global.asax, add Symber.Web.Compilation.APGenManager.SyncAndInitData(); in the Application_Start method, that will be automatically create and maintain the DATABASE and initialization data.

About the .agpen file

Look at here simple, the details please refer to the documents.

<businessModel autoSyncDatabase="true" autoInitDatabase="true">
  <tables>
    <table name="Department" comment="Departments of company">
      <columns>
        <add name="DepartmentId" type="int" primaryKey="true" identityType="Provider"/>
        <add name="ParentId" type="int" comment="Parent DepartmentId"/>
        <add name="DeptName" type="string" dataLength="20"/>
        <add name="Phone" type="string" dataLength="20"/>
      </columns>
      <uniques>
        <index name="IX_Department_DeptName">
          <add name="DeptName" according="Asc"/>
        </index>
      </uniques>
      <aliases>
        <add name="Parent"/>
      </aliases>
    </table>

    <table name="Employee" comment="Employees of company">
      <columns>
        <add name="EmployeeId" type="int" primaryKey="true" identityType="Provider"/>
        <add name="DepartmentId" type="int" comment="Employee's DeparentId"/>
        <add name="Name" type="string" dataLength="30"/>
        <add name="Birthday" type="DateTime"/>
        <add name="Email" type="string" dataLength="255"/>
      </columns>
      <indexes>
        <index name="IX_Employee_Name" isDefault="true">
          <add name="Name" according="Asc"/>
        </index>
      </indexes>
    </table>			 
  </tables>
</businessModel>

What objectizations does we have

---------------------------------------------------------------------
Entity Defined                          |   Department
---------------------------------------------------------------------
Database Struct Defined                 |   APDBDef.Department
---------------------------------------------------------------------
Data Access Layout (Dal) Defined        |   APDalDef.DepartmentDal
---------------------------------------------------------------------
Business Process Logic (Bpl) Defined    |   APBplDef.DepartmentBpl
---------------------------------------------------------------------

Usage of ORM

Insert a data

var dep = new Department(1, 0, "Sales", "000-000-0000");
dep.Insert();

Delete a data

Department.PrimaryDelete(1);

Condition delete

// short refer name
var t = APDBDef.Department;
Department.ConditionDelete(t.ParentId == 0 & t.DeptName != "Sales");

Get data with primary key and update

var dep = Department.PrimaryGet(1);
dep.Phone = "000-000-0001";
dep.Update();

Partial update

Department.UpdatePartial(1, new { Phone="000-000-5555", DeptName="New Seals"});

Condition query and order

var t = APDBDef.Department;
List<Department> list = Department.ConditionQuery(
   t.ParentId == 0 & t.DeptName != "Sales",  // condition
   t.DepartmentId.Desc);                     // order

Paging query

var t = APDBDef.Department;
List<Department> list = Department.ConditionQuery(
   t.ParentId == 0 & t.DeptName != "Sales",  // condition
   t.DepartmentId.Desc,                      // order
   20, 20);       // take 20 records, skip ahead 20 records

Query count

Department.ConditionQueryCount(t.ParentId != 0);

Transcation and Dal

using (APDBDef db = new APDBDef())
{
   db.BeginTrans();

   try
   {
      db.DepartmentDal.PrimaryDelete(1);
      db.EmployeeDal.ConditionDelete(APDBDef.Employee.DepartmentId == 1);

      db.Commit();
   }
   catch
   {
      db.Rollback();
   }
}

Usage of SQL Expression

Sometimes the ORM can't meet our requirement, so we can directly use SQL Expression. Of course, the core of ORM is also dependent on SQL Expression.

Asterisk Query

var dep = APDBDef.Department;
using (APDBDef db = new APDBDef())
{
   IEnumerable<Department> result = APQuery
      .select(dep.Asterisk).distinct()
      .from(dep)
      .where(dep.ParentId == 0 & dep.Phone != null)
      .order(dep.DeptName.Desc)
      .query(db, dep.Map);
}

Execute on SQLServer provider.

SELECT DISTINCT Department.* 
  FROM Department
  WHERE Department.ParentId = 0 AND Department.Phone IS NOT NULL
  ORDER BY Department.DeptName DESC

In the following, we only write SQL Expression.

Value and through expression (New)

'' is a escape character, when the first letter is '' while be parse after the letter through.

APQuery
   .select(dep.DepartmentId, 3, "abc", "~3*3", null)
   .from(dep);

SELECT Department.DepartmentId, 3, 'abc', 3*3, NULL
  FROM Department

Column Alias

APQuery
   .select(dep.DepartmentId, dep.DeptName.As("Name"), dep.Phone.As("Dept Phone"))
   .from(dep);

SELECT Department.DepartmentId, Department.DeptName AS Name,
       Department.Phone AS [Dept Phone]
  FROM Department

Multi-table query

var d = APDBDef.Department;
var e = APDBDef.Employee;
APQuery
   .select(e.EmployeeId, d.DeptName, e.Name)
   .from(d, e.JoinInner(d.DepartmentId == e.DepartmentId))
   .where(d.ParentId.NotIn(2, 3, 4));

SELECT Employee.EmployeeId, Department.DeptName, Employee.Name
  FROM Department
  INNER JOIN Employee ON Department.DepartmentId == Employee.DepartmentId
  WHERE Department.DepartmentId IN ( 2, 3, 4 )

Alias table query

var d = APDBDef.Department;
var dp = APDBDef.Department.Parent;
APQuery
   .select(d.DepartmentId, d.DeptName, dp.DeptName.As ("ParentName"))
   .from(d, dp.JoinLeft(d.ParentId == dp.DepartmentId))
   .where(dp.DeptName.Match("ale"));

SELECT Department.DepartmentId, Department.DeptName, Parent.DeptName AS 'ParentName'
  FROM Department
  LEFT JOIN Department AS Parent ON Department.ParentId == Parent.DepartmentId
  WHERE Parent.DeptName LIKE '%ale%'

Subquery

var d = APDBDef.Department;
var subQuery = APQuery
   .select(d.DepartmentId)
   .from(d)
   .where(d.ParentId == 0);
APQuery
   .select(d.Asterisk)
   .from(d)
   .where(subQuery.exist());

SELECT Department.*
  FROM Department
  WHERE ( EXISTS (
    SELECT Department.DepartmentId
      FROM Department
		WHERE Department.ParentId = 0
  ) )

Paging Query

var d = APDBDef.Department;
var e = APDBDef.Employee;
var query = APQuery
   .select(e.EmployeeId, e.Name, d.DeptName)
   .from(e, d.JoinInner(e.DepartmentId == d.DepartmentId))
   .primary(e.EmployeeId)
   .take(20)
   .skip(20);

using (APDBDef db = new APDBDef())
{
   int total = db.ExecuteSizeOfSelect(query);
   IDataReader records = db.ExecuteReader(query);
}

Execute on SQLServer provider.

SELECT COUNT(*)
  FROM Employee,
    INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId

SELECT TOP 20 Employee.EmployeeId, Employee.Name, Department.DeptName
  FROM Employee,
    INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId
  WHERE Employee.EmployeeId NOT IN (
    SELECT TOP 20 Employee.EmployeeId
	   FROM Employee
		  INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId
  )

Execute on Oracle provider.

SELECT COUNT(*)
  FROM Employee,
    INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId

SELECT * FROM ( SELECT query_alias.*, ROWNUM query_rownum FROM (
  SELECT Employee.EmployeeId, Employee.Name, Department.DeptName
    FROM Employee,
      INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId
  ) query_alias WHERE ROWNUM <= 40 ) WHERE query_rownum > 20)

Aggregation & Group By

var t = APDBDef.Department;
var e = APDBDef.Employee;
APQuery
   .select(t.DepartmentId, t.DeptName, e.EmployeeId.Count())
   .from(t, e.JoinLeft(t.DepartmentId == e.DepartmentId))
   .group_by(t.DepartmentId, t.DeptName, e.EmployeeId)
   .having(e.EmployeeId.Count() > 0);

SELECT Department.DepartmentId, Department.DeptName, COUNT(Employee.EmployeeId)
FROM Department
     LEFT JOIN Employee ON Department.DepartmentId = Employee.DepartmentId
GROUP BY Department.DepartmentId, Department.DeptName, Employee.EmployeeId
HAVING COUNT(Employee.EmployeeId) > 0

Aggregation with Date

var t = APDBDef.Employee;
APQuery
   .select(t.Birthday.DateGroup(APSqlDateGroupMode.Month),
	   new APSqlAggregationExpr(t.Birthday.DateGroup(APSqlDateGroupMode.Month), APSqlAggregationType.COUNT)))
   .from(t)
   .group_by(t.Birthday.DateGroup(APSqlDateGroupMode.Month));

Execute on SQLServer provider.

SELECT DATEADD( mm, DATEDIFF( mm, 0, Employee.Birthday ), 0 ),
   COUNT( DATEADD( mm, DATEDIFF( mm, 0, Employee.Birthday ), 0 ) )
   FROM Employee
   GROUP BY DATEADD( mm, DATEDIFF( mm, 0, Employee.Birthday ), 0 )

Execute on Oracle provider.

SELECT to_char(Employee.Birthday, 'yyyy-mm' ),
   COUNT( to_char(Employee.Birthday, 'yyyy-mm' ) )
   FROM Employee
   GROUP BY to_char(Employee.Birthday, 'yyyy-mm' )

Insert

var d = APDBDef.Department;
APQuery
   .insert(d)
      .set(d.DepartmentId, 5)
      .set(d.DeptName, "HR")
      .set(d.Phone, "000-111-2222");

INSERT INTO Department
  (Department.DepartmentId, Department.DeptName, Department.Phone)
  Values (5, 'HR', '000-111-2222')

Update

var d = APDBDef.Department;
APQuery
   .update(d)
      .set(d.Phone, "000-111-3333")
   .where(d.DepartmentId == 5);

UPDATE Department
    SET Department.Phone = '000-111-3333'
  WHERE Department.DepartmentId = 5

Delete

var d = APDBDef.Department;
APQuery
   .delete(d)
   .where(d.ParentId == 0);

DELETE Department
  WHERE Department.ParentId = 0

Anonymous return

var d = APDBDef.Department;
var query = APQuery
   .select(d.DepartmentId, d.DeptName)
   .from(d);

using (APDBDef db = new APDBDef())
{
   var records = db.Query(query, r =>
   {
      return new
      {
         id = d.DepartmentId.GetValue(r),
         name = d.DeptName.GetValue(r)
      };
   });
}

Copyright and license

Copyright (c) 2014-2015 Leo Caan. The license see LICENSE file.