public void ExpressionGenerator_DbCaseExpression()
{
var _ = new TestModel();
var column1 = _.Column1;
_Int32 c1 = _Int32.Const(1);
_Int32 c0 = _Int32.Const(0);
{
var expr = Case.On(column1)
.When(c1).Then(_Boolean.True)
.When(c0).Then(_Boolean.False)
.Else(_Boolean.Null);
var expectedSql =
@"CASE `TestModel`.`Column1`
WHEN 1 THEN 1
WHEN 0 THEN 0
ELSE NULL
END CASE";
VerifyDbExpression(MySqlVersion.LowestSupported, expr.DbExpression, expectedSql);
}
{
var expr = Case.When(column1 == c1).Then(_Boolean.True)
.When(column1 == c0).Then(_Boolean.False)
.Else(_Boolean.Null);
var expectedSql =
@"CASE
WHEN (`TestModel`.`Column1` = 1) THEN 1
WHEN (`TestModel`.`Column1` = 0) THEN 0
ELSE NULL
END CASE";
VerifyDbExpression(MySqlVersion.LowestSupported, expr.DbExpression, expectedSql);
}
}
public async Task <DataSet <SalesOrderInfo> > GetSalesOrderInfoAsync(_Int32 salesOrderID, CancellationToken ct = default(CancellationToken))
{
var result = CreateQuery((DbQueryBuilder builder, SalesOrderInfo _) =>
{
builder.From(SalesOrderHeader, out var o)
.LeftJoin(Customer, o.FK_Customer, out var c)
.LeftJoin(Address, o.FK_ShipToAddress, out var shipTo)
.LeftJoin(Address, o.FK_BillToAddress, out var billTo)
.AutoSelect()
.AutoSelect(c, _.Customer)
.AutoSelect(shipTo, _.ShipToAddress)
.AutoSelect(billTo, _.BillToAddress)
.Where(o.SalesOrderID == salesOrderID);
});
await result.CreateChildAsync(_ => _.SalesOrderDetails, (DbQueryBuilder builder, SalesOrderInfoDetail _) =>
{
builder.From(SalesOrderDetail, out var d)
.LeftJoin(Product, d.FK_Product, out var p)
.AutoSelect()
.AutoSelect(p, _.Product)
.OrderBy(d.SalesOrderDetailID);
}, ct);
return(await result.ToDataSetAsync(ct));
}
public void ExpressionGenerator_DbCaseExpression()
{
var model = new TestModel();
var column1 = CreateColumn <_Int32>(model, "Column1");
_Int32 c1 = _Int32.Const(1);
_Int32 c0 = _Int32.Const(0);
{
var expr = Case.On(column1)
.When(c1).Then(_Boolean.True)
.When(c0).Then(_Boolean.False)
.Else(_Boolean.Null);
var expectedSql =
@"CASE [TestModel].[Column1]
WHEN 1 THEN 1
WHEN 0 THEN 0
ELSE NULL
END";
VerifyDbExpression(SqlVersion.Sql13, expr.DbExpression, expectedSql);
}
{
var expr = Case.When(column1 == c1).Then(_Boolean.True)
.When(column1 == c0).Then(_Boolean.False)
.Else(_Boolean.Null);
var expectedSql =
@"CASE
WHEN ([TestModel].[Column1] = 1) THEN 1
WHEN ([TestModel].[Column1] = 0) THEN 0
ELSE NULL
END";
VerifyDbExpression(SqlVersion.Sql13, expr.DbExpression, expectedSql);
}
}
public Task <DataSet <Address> > GetAddressLookupAsync(_Int32 customerID, CancellationToken ct)
{
var result = CreateQuery <Address>((builder, _) =>
{
builder.From(CustomerAddress.Where(x => x.CustomerID == customerID), out var ca)
.InnerJoin(Address, ca.FK_Address, out var a)
.AutoSelect();
});
return(result.ToDataSetAsync(ct));
}
public static DbSet <T> CreateJsonTable <T>(this MySqlSession mySqlSession, string json, string ordinalColumnName)
where T : Model, new()
{
var _ = new T();
if (!string.IsNullOrEmpty(ordinalColumnName))
{
var dataSetOrdinalColumn = new _Int32().AsJsonOrdinality();
_.AddSystemColumn(dataSetOrdinalColumn, ordinalColumnName);
}
var sourceJsonParam = _String.Param(json).AsMySqlJson();
s_jsonRowSets.Add(_, sourceJsonParam);
return(_.CreateDbTable(mySqlSession, ModelAliasManager.GetDbAlias(_)));
}
public static DbSet <T> CreateJsonRowSet <T>(this SqlSession sqlSession, string json, string ordinalColumnName)
where T : class, IEntity, new()
{
var _ = new T();
var model = _.Model;
if (!string.IsNullOrEmpty(ordinalColumnName))
{
var dataSetOrdinalColumn = new _Int32();
model.AddSystemColumn(dataSetOrdinalColumn, ordinalColumnName);
}
var sourceJsonParam = _String.Param(json).AsSqlNVarCharMax();
s_jsonRowSets.Add(model, sourceJsonParam);
return(_.CreateDbTable(sqlSession, ModelAliasManager.GetDbAlias(model)));
}
public async Task DbTable_UpdateAsync_self_increment()
{
var log = new StringBuilder();
using (var db = await MockSalesOrder.CreateAsync(CreateDb(log)))
{
_Int32 salesOrderId = 1;
var dataSet = await db.SalesOrderHeader.Where(_ => _.SalesOrderID == salesOrderId).ToDataSetAsync();
Assert.AreEqual(1, dataSet.Count);
var revisionNumber = dataSet._.RevisionNumber[0];
await db.SalesOrderHeader.UpdateAsync((m, _) => m.Select(_.RevisionNumber + 1, _.RevisionNumber), _ => _.SalesOrderID == salesOrderId);
dataSet = await db.SalesOrderHeader.Where(_ => _.SalesOrderID == salesOrderId).ToDataSetAsync();
Assert.AreEqual(1, dataSet.Count);
Assert.AreEqual(revisionNumber + 1, dataSet._.RevisionNumber[0]);
}
}
public PK(_Int32 productCategoryID)
: base(productCategoryID)
{
}
public PK(_Int32 salesOrderID)
: base(salesOrderID)
{
}
public PK(_Int32 customerID)
: base(customerID)
{
}
public PK(_Int32 addressID)
: base(addressID)
{
}
public Int32IdentityUpdater(DataSet dataSet, _Int32 identityColumn, Int32 lastInsertId)
: base(dataSet, identityColumn, lastInsertId)
{
}
public PrimaryKeyMismatchBaseConstructorArgument(_Int32 id1, _Int32 id2)
: base(id2, id1)
{
}
public PK(_Int32 customerId)
: base(customerId)
{
}
public PrimaryKeySortAttributeConflict([Asc][Desc] _Int32 id)
: base(id)
{
}
public PrimaryKeyInvalidConstructorParam(int id1, _Int32 id2, int id3)
: base(id2)
{
}
public PK(_Int32 customerID, _Int32 addressID)
: base(customerID, addressID)
{
}
public PK(_Int32 salesOrderID, _Int32 salesOrderDetailID)
: base(salesOrderID, salesOrderDetailID)
{
}
public PK(_Int32 id2)
: base(id2)
{
}
internal static new TempTableIdentity FromInt32Column(_Int32 column, int seed, int increment)
{
return(new TempTableIdentity(column, seed, increment));
}
public PrimaryKeyMissingBaseConstructor(_Int32 id)
{
}
public PrimaryKeyMismatchBaseConstructor(_Int32 id)
: base()
{
}
public PK(_Int32 addressId)
: base(addressId)
{
}
public PK(_Int32 productDescriptionID)
: base(productDescriptionID)
{
}
public Task <DataSet <Movie> > GetMovieAsync(_Int32 id, CancellationToken ct = default(CancellationToken))
{
return(Movie.Where(_ => _.ID == id).ToDataSetAsync(ct));
}