codeer-software / lambdicsql.sqlserver Goto Github PK

join same table, need table alias.

cs

class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}
class Order
{
    public int Id { get; set; }
    public int? ContactUserId { get; set; }
    public int? CreatedByUserId { get; set; }
}

class DB
{
    public User User { get; set; }
    public Order Order { get; set; }
}

Db<DB>.Sql(db => Select(new
{
    Id = db.Order.Id,
    ContactUserName = db.User.Name,
    CreatedByUserName = db.User.Name
}).From(db.Order).LeftJoin(db.User, db.Order.ContactUserId == db.User.Id)
    .LeftJoin(db.User, db.Order.CreatedByUserId == db.User.Id));

output query

SELECT
	Order.Id AS Id,
	User.Name AS ContactUserName,
	User.Name AS CreatedByUserName
FROM Order
	LEFT JOIN User ON Order.ContactUserId = User.Id
	LEFT JOIN User ON Order.CreatedByUserId = User.Id

I want to write query.

SELECT
	Order.Id AS Id,
	User_Contact.Name AS ContactUserName,
	User_CreatedBy.Name AS CreatedByUserName
FROM Order
	LEFT JOIN User User_Contact ON Order.ContactUserId = User_Contact.Id
	LEFT JOIN User User_CreatedBy ON Order.CreatedByUserId = User_CreatedBy.Id

cs

class DB {
    public Hoge Hoge { get; set; }
}
class Hoge {
    public decimal Val1 { get; set; }
    public decimal Val2 { get; set; }
    public decimal Val3 { get; set; }
}

var errorQuery = Db<DB>.Sql(db => CreateTable(
    db.Hoge,
    new Column(db.Hoge.Val1, DataType.Decimal()),
    new Column(db.Hoge.Val2, DataType.Decimal(8)),
    new Column(db.Hoge.Val3, DataType.Decimal(8, 0))
));

output sql

CREATE TABLE Hoge(
	Val1 DECIMAL,
	Val2 DECIMAL 8,
	Val3 DECIMAL 8, 0)

output sql is syntax error.

	Val2 DECIMAL 8,
	Val3 DECIMAL 8, 0

expected query.

CREATE TABLE Hoge(
	Val1 DECIMAL,
	Val2 DECIMAL(8),
	Val3 DECIMAL(8, 0))

@Ishikawa-Tatsuya さん、このテストがDapperで例外起こすんですが、理由わかります？

https://github.com/Codeer-Software/LambdicSql.SqlServer/compare/kkamegawa/20170401#diff-4df9bd3a0bf122681b21da724a1abaddR174

テスト名: Test_Ascii
テストの完全名: Test.TestSymbolFuncsString.Test_Ascii
テスト ソース: C:\GitHub\LambdicSql.SqlServer\Project\Test40\TestSymbolFuncsString.cs : 行 175
テスト成果: 失敗
テスト継続時間: 0:01:06.0833638

結果 のスタック トレース:
場所 LambdicSql.feat.Dapper.DapperAdapter.Query[T](IDbConnection cnn, BuildedSql sql, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType)
場所 LambdicSql.feat.Dapper.DapperAdapter.Query[T](IDbConnection cnn, Sql sql, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType)
場所 LambdicSql.feat.Dapper.DapperAdapter.Query[T](IDbConnection cnn, Sql1 sql, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) 場所 Test.TestSymbolFuncsString.Test_Ascii() 場所 C:\GitHub\LambdicSql.SqlServer\Project\Test40\TestSymbolFuncsString.cs:行 181 結果 のメッセージ: テスト メソッド Test.TestSymbolFuncsString.Test_Ascii が例外をスローしました: System.InvalidOperationException: A parameterless default constructor or one matching signature (System.Int32 Val) is required for <>f__AnonymousType61[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] materialization

If you use SQL Database with Unit Test, you must pay SQL Database cost.Because test uses Create/drop Database(S0: 2.33 Japanese Yen).

Add Test priority.

class DB {
    public Hoge Hoge { get; set; }
    public Fuga Fuga { get; set; }
}
class Hoge {
    public long Id { get; set; }
    public long FugaId { get; set; }
}
class Fuga {
    public long Id { get; set; }
}

var q= Db<DB>.Sql(db => Select(Asterisk(db.Hoge))
  .From(db.Hoge).Join(db.Fuga, db.Hoge.FugaId == db.Fuga.Id));

output sql

SELECT *
FROM Hoge 
	JOIN Fuga ON Hoge.FugaId = Fuga.Id

but expected sql

SELECT Hoge.*
FROM Hoge 
	JOIN Fuga ON Hoge.FugaId = Fuga.Id

Schedule build( 7:00AM JST) status shows readme.md

reproduce

var sql = Db<DB>.Sql(db =>
  Select(new
  {  name = Cast<string>(db.tbl_staff.name, DataType.NVarChar(20)) })
  .From(db.tbl_staff));

expected

SELECT
	CAST(tbl_staff.name AS NVARCHAR(20)) AS name
FROM tbl_staff

actual

SELECT
	CAST(tbl_staff.name AS NVARCHAR(@p_0)) AS name
FROM tbl_staff

class DB {
    public Order Order { get; set; }
}
class Hoge {
    public long Id { get; set; }
    public string Name { get; set; }
}

var errorQuery = Db<DB>.Sql(db => Select(new
 {
    Id = db.Order.Id,
    Count = Count(Asterisk()).Over()
}).From(db.Order));

output sql

SELECT
	Order.Id AS Id,
	COUNT(*)
	OVER( AS Count
FROM Order

expected sql

SELECT
	Order.Id AS Id,
	COUNT(*) 
        OVER() AS Count
FROM Order

Hi,
First of all, i really appreciate this work.
I have few things to ask which either i am unable to find or not there yet.

1. for every select column (e.g. Name = db.tbl_staff.name), it ask for property name which becomes alias (e.g. As Name). I think, this you be optional if property name is there then make it alias otherwise no alias. something like anonymous object in Select expression "Expression<Func<T, object>> expression"

2. is there any option in form of annotation on property and entity name to override their names while sql generation?

3. If clause (IF sql Then sql)?

4. is there a way to reduce the actual sql size by using sql table alias (select a.name from tableName a) instead [tableName].[columnName]

Symbol.Funcs.String.cs is a part of implement in String functions.
https://docs.microsoft.com/ja-jp/sql/t-sql/functions/string-functions-transact-sql

cs

class DB {
    public Hoge Hoge { get; set; }
    public Fuga Fuga { get; set; }
}
class Hoge {
    public long Id { get; set; }
    public long FugaId { get; set; }
}
class Fuga {
    public long Id { get; set; }
}

var errorQuery = Db<DB>.Sql(db => CreateTable(
    db.Hoge,
    new Column(db.Hoge.Id, DataType.BigInt(), NotNull(), PrimaryKey()),
    new Column(db.Hoge.FugaId, DataType.BigInt(), ForeignKey().References(db.Fuga, db.Fuga.Id))
));

output sql

CREATE TABLE Hoge(
	Id BIGINT NOT NULL PRIMARY KEY,
	FugaId BIGINT FOREIGN KEY() REFERENCES Fuga(Id)
)

output sql is syntax error.

FOREIGN KEY() REFERENCES Fuga(Id)

expected query

CREATE TABLE Hoge(
	Id BIGINT NOT NULL PRIMARY KEY,
	FugaId BIGINT FOREIGN KEY REFERENCES Fuga(Id)
)

Build error in csproj build sequence.
Build success:

1. .NET 4.6's csproj
2. .NET 3.5's csproj

Build failed:

1. .NET 3.5's csproj
2. .NET 4.6's csproj

document comment illegal.