codeer-software / lambdicsql.sqlserver Goto Github PK
View Code? Open in Web Editor NEWLicense: MIT License
License: MIT License
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で例外起こすんですが、理由わかります?
テスト名: 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, Nullable
1 commandType)
場所 LambdicSql.feat.Dapper.DapperAdapter.Query[T](IDbConnection cnn, Sql sql, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable
1 commandType)
場所 LambdicSql.feat.Dapper.DapperAdapter.Query[T](IDbConnection cnn, Sql1 sql, IDbTransaction transaction, Boolean buffered, Nullable
1 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__AnonymousType6
1[[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.
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"
is there any option in form of annotation on property and entity name to override their names while sql generation?
If clause (IF sql Then sql)?
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:
Build failed:
document comment illegal.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.