public void FieldNameLeftJoinTest()
{
if (OrmLiteConfig.UseParameterizeSqlExpressions)
{
return;
}
var joinQuery = new JoinSqlBuilder <User, User> ().LeftJoin <User, Address> (x => x.Id, x => x.UserId).ToSql();
var expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Address\" ON \"User\".\"Id\" = \"Address\".\"UserId\" \n".NormalizeSql();
var expectedNq = "SELECT \"User\".Id,\"User\".Name,\"User\".Age \nFROM \"User\" \n LEFT OUTER JOIN Address ON \"User\".Id = Address.UserId \n".NormalizeSql();
Assert.That(joinQuery.NormalizeSql(), Is.EqualTo(expected).Or.EqualTo(expectedNq));
joinQuery = new JoinSqlBuilder <WithAliasUser, WithAliasUser> ().LeftJoin <WithAliasUser, WithAliasAddress> (x => x.Id, x => x.UserId).ToSql();
expected = "SELECT \"Users\".\"Id\",\"Users\".\"Nickname\",\"Users\".\"Agealias\" \nFROM \"Users\" \n LEFT OUTER JOIN \"Addresses\" ON \"Users\".\"Id\" = \"Addresses\".\"UserId\" \n".NormalizeSql();
expectedNq = "SELECT Users.Id,Users.Nickname,Users.Agealias \nFROM Users \n LEFT OUTER JOIN Addresses ON Users.Id = Addresses.UserId \n".NormalizeSql();
Assert.That(joinQuery.NormalizeSql(), Is.EqualTo(expected).Or.EqualTo(expectedNq));
joinQuery = new JoinSqlBuilder <User, User> ().LeftJoin <User, WithAliasAddress> (x => x.Id, x => x.UserId).ToSql();
expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Addresses\" ON \"User\".\"Id\" = \"Addresses\".\"UserId\" \n".NormalizeSql();
expectedNq = "SELECT \"User\".Id,\"User\".Name,\"User\".Age \nFROM \"User\" \n LEFT OUTER JOIN Addresses ON \"User\".Id = Addresses.UserId \n".NormalizeSql();
Assert.That(joinQuery.NormalizeSql(), Is.EqualTo(expected).Or.EqualTo(expectedNq));
}
public List <T> GetByFilter(BaseFilter filter)
{
using (var unitOfWork = UnitOfWorkProvider.GetUnitOfWork())
{
var builder = new JoinSqlBuilder <T, T>();
if (filter.Id.HasValue)
{
builder.Where <T>(x => x.Id == filter.Id);
}
if (filter.Created.HasValue)
{
builder.Where <T>(x => x.Created == filter.Created);
}
if (filter.Updated.HasValue)
{
builder.Where <T>(x => x.Updated == filter.Updated);
}
AddFilterOption(builder);
return(unitOfWork.DB.Select <T>(builder.ToSql()));
}
}
public void Can_execute_JoinSqlBuilder_as_SqlExpression()
{
if (OrmLiteConfig.UseParameterizeSqlExpressions)
{
return;
}
var joinQuery = new JoinSqlBuilder <User, User>()
.LeftJoin <User, WithAliasAddress>(x => x.Id, x => x.UserId
, sourceWhere: x => x.Age > 18
, destinationWhere: x => x.Country == "Italy");
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <User>();
db.DropAndCreateTable <WithAliasAddress>();
var userId = db.Insert(new User {
Age = 27, Name = "Foo"
}, selectIdentity: true);
db.Insert(new WithAliasAddress {
City = "Rome", Country = "Italy", UserId = (int)userId
});
var results = db.Select <User>(joinQuery);
Assert.That(results.Count, Is.EqualTo(1));
}
}
public string GenerateFetchParticipantSql(IParticipantRequest request)
{
var fetchRequest = request as FetchParticipantStatus;
var fetchAllReqeust = request as FetchParticipantStatuses;
var jn = new JoinSqlBuilder<Participant, Participant>()
.Join<Crawler, Participant>(c => c.Id, p => p.CrawlerId)
.Join<Participant, Division>(p => p.DivisionId, d => d.Id)
.Join<Crawler, Server>(c => c.ServerId, d => d.Id)
.Select<Crawler>(c => new { c.UserName, c.ServerId })
.Select<Participant>(p => new { p.CrawlerId, p.LastGame, p.SeasonId, p.DivisionId })
.Select<Division>(d => new { DivisionName = d.Name })
.Select<Server>(s => new { ServerName = s.Name, ServerAbbreviation = s.Abbreviation })
.Where<Participant>(s => s.SeasonId == request.SeasonId);
jn.OrderByDescending<Participant>(p => p.Score);
// Restricted by Crawler
if (fetchRequest != null)
jn.And<Participant>(p => p.CrawlerId == fetchRequest.CrawlerId);
// Standings
if (fetchAllReqeust != null) // Hackaroo pageroo for JoinSqlBuilder.
{
jn.And<Division>(d => d.Id == fetchAllReqeust.DivisionId);
return (jn.ToPagedSql(fetchAllReqeust.Page ?? 1));
}
return (jn.ToSql());
}
public async Task <IEnumerable <IUserDTO> > FindAll(bool enabledRequired)
{
List <IUserDTO> users = new List <IUserDTO>();
var sqlQuery = new JoinSqlBuilder <TUser, TRole>(this.Connection.DialectProvider)
.Join <TRole, TUser>(i => i.Id, i => i.RoleId)
.SelectAll <TUser>()
.Select <TRole>(p => new { RoleName = p.Name })
.ToSql();
var result = (await this.Connection.QueryAsync <TUser>(sqlQuery)).OrderByDescending(i => i.Id);
foreach (TUser obj in result)
{
UserDTO user = new UserDTO();
user.UserId = obj.Id;
user.UserEmail = obj.Email;
user.UserName = obj.UserName;
user.Enabled = obj.Enabled;
user.PhoneNumber = obj.PhoneNumber;
user.RoleId = obj.RoleId;
user.CoordinatorId = obj.CoordinatorId;
user.AccountId = obj.AccountId;
user.RoleName = obj.RoleName;
user.ClientName = obj.ClientName;
user.CoordinatorId = obj.CoordinatorId;
user.CoordinatorName = obj.CoordinatorName;
users.Add(user);
}
return(users);
}
public async Task <string> UsedOrNotInWorkOrder(long id)
{
string returnValue = "";
try
{
var sqlQuery = new JoinSqlBuilder <TFacility, TWorkOrder>(this.Connection.DialectProvider)
.Join <TFacility, TWorkOrder>(i => i.Id, i => i.FacilityId)
.Select <TWorkOrder>(p => new { Id = p.Id })
.Where <TWorkOrder>(ri => ri.RequestTypeId == id)
.ToSql();
var result = await this.Connection.QueryFirstOrDefaultAsync <TWorkOrder>(sqlQuery, new { p_0 = id });
if (result != null)
{
returnValue = result.Id.ToString();
}
}
catch (PostgresException ex)
{
throw new EntityUpdateException(ex);
}
catch (Exception ex)
{
throw;
}
return(returnValue);
}
public void ComplexJoin_with_JoinSqlBuilder()
{
using (var db = OpenDbConnection())
{
InitTables(db);
/* This gives the expected values for BazId */
var jn = new JoinSqlBuilder <JoinResult, FooBar>()
.Join <FooBar, BarJoin>(
sourceColumn: dp => dp.BarId,
destinationColumn: p => p.Id,
destinationTableColumnSelection: p => new { BarName = p.Name, BarId = p.Id })
.Join <FooBar, FooBarBaz>(
sourceColumn: dp => dp.Id,
destinationColumn: dpss => dpss.FooBarId,
destinationTableColumnSelection: dpss => new { dpss.Amount, FooBarBazId = dpss.Id });
jn.Join <FooBarBaz, Baz>(
sourceColumn: dpss => dpss.BazId,
destinationColumn: ss => ss.Id,
destinationTableColumnSelection: ss => new { BazId = ss.Id, BazName = ss.Name });
jn.Select <FooBar>(dp => new { Id = dp.Id, });
var results = db.Select <JoinResult>(jn.ToSql());
db.GetLastSql().Print();
results.PrintDump();
var fooBarBaz = results.First(x => x.FooBarBazId == _fooBarBaz1Id);
Assert.That(fooBarBaz.BazId, Is.EqualTo(_baz2Id));
fooBarBaz = results.First(x => x.FooBarBazId == _fooBarBaz2Id);
Assert.That(fooBarBaz.BazId, Is.EqualTo(_baz1Id));
fooBarBaz = results.First(x => x.FooBarBazId == _fooBarBaz2Id);
Assert.That(fooBarBaz.BazId, Is.EqualTo(_baz1Id));
}
}
public void DoubleWhereLeftJoinTest()
{
var joinQuery = new JoinSqlBuilder <User, User> ().LeftJoin <User, WithAliasAddress> (x => x.Id, x => x.UserId
, sourceWhere: x => x.Age > 18
, destinationWhere: x => x.Country == "Italy").ToSql();
var expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Addresses\" ON \"User\".\"Id\" = \"Addresses\".\"UserId\" \nWHERE (\"User\".\"Age\" > 18) AND (\"Addresses\".\"Countryalias\" = 'Italy') \n";
Assert.AreEqual(expected, joinQuery);
}
public void DoubleWhereLeftJoinTest ()
{
var joinQuery = new JoinSqlBuilder<User, User> ().LeftJoin<User, WithAliasAddress> (x => x.Id, x => x.UserId
, sourceWhere: x => x.Age > 18
, destinationWhere: x => x.Country == "Italy").ToSql ();
var expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Addresses\" ON \"User\".\"Id\" = \"Addresses\".\"UserId\" \nWHERE (\"User\".\"Age\" > 18) AND (\"Addresses\".\"Countryalias\" = 'Italy') \n";
Assert.AreEqual (expected, joinQuery);
}
public void SelectCountTest()
{
var joinQuery =
new JoinSqlBuilder <User, User>().LeftJoin <User, Address>(x => x.Id, x => x.UserId)
.SelectCount <User>(x => x.Id).ToSql();
var expected =
"SELECT COUNT(\"User\".\"Id\") \nFROM \"User\" \n LEFT OUTER JOIN \"Address\" ON \"User\".\"Id\" = \"Address\".\"UserId\" \n";
Assert.AreEqual(expected, joinQuery);
}
public object Get(FindLinks request)
{
var jn = new JoinSqlBuilder<LinkEx, Link>();
jn = jn.Join<Link, Page>(x => x.PageId, x => x.Id, x => new {x.Id, x.Title, x.Url},
x => new {PageTitle = x.Title});
if (request.Id != default(int))
{
jn.Where<Link>(x => x.Id == request.Id);
var sql = jn.ToSql();
return Db.Select<LinkEx>(sql);
}
if (request.Title != default(string) && request.Url != default(string))
{
if (request.ExactMatch)
jn.Where<Link>(x => x.Title == request.Title && x.Url == request.Url);
else
{
jn.Where<Link>(x => x.Title.Contains(request.Title) && x.Url.Contains(request.Url));
}
var sql = jn.ToSql();
return Db.Select<LinkEx>(sql);
}
if (request.Title != default(string))
{
if (request.ExactMatch)
jn.Where<Link>(x => x.Title == request.Title);
else
{
jn.Where<Link>(x => x.Title.Contains(request.Title) );
}
var sql = jn.ToSql();
return Db.Select<LinkEx>(sql);
}
if (request.Url != default(string))
{
if (request.ExactMatch)
jn.Where<Link>(x => x.Url == request.Url);
else
{
jn.Where<Link>(x => x.Url.Contains(request.Url));
}
var sql = jn.ToSql();
return Db.Select<LinkEx>(sql);
}
return "nothing";
}
public void SelectCountDistinctTest()
{
OrmLiteConfig.DialectProvider = SqliteDialect.Provider;
var joinQuery =
new JoinSqlBuilder <User, User>().LeftJoin <User, Address>(x => x.Id, x => x.UserId)
.SelectCountDistinct <User>(x => x.Id).ToSql();
var expected =
"SELECT COUNT(DISTINCT \"User\".\"Id\") \nFROM \"User\" \n LEFT OUTER JOIN \"Address\" ON \"User\".\"Id\" = \"Address\".\"UserId\" \n";
Assert.AreEqual(expected, joinQuery);
}
public ActionResult List(int cat_id)
{
List <Product> c = new List <Product>();
JoinSqlBuilder <Product, Product> jn = new JoinSqlBuilder <Product, Product>();
if (cat_id > 0)
{
//jn = jn.Join<Product, Product_In_Category>(m => m.Id, k => k.ProductId);
jn = jn.Where <Product>(m => m.CatId == cat_id);
}
jn = jn.OrderBy <Product>(x => x.Order);
var sql = jn.ToSql();
c = Db.Select <Product>(sql);
var list_users = Cache_GetAllUsers();
var cats = Db.Select <Product_Category>();
foreach (var x in c)
{
var z = list_users.Where(m => m.Id == x.CreatedBy);
if (z.Count() > 0)
{
var k = z.First();
if (string.IsNullOrEmpty(k.FullName))
{
x.CreatedByUsername = k.UserName;
}
else
{
x.CreatedByUsername = k.FullName;
}
}
else
{
x.CreatedByUsername = "******";
}
var kk = cats.Where(q => q.Id == x.CatId).FirstOrDefault();
if (kk != null)
{
x.Category_Name = kk.Name;
}
else
{
x.Category_Name = "Deleted Category";
}
}
return(PartialView("_List", c));
}
public async Task <IEnumerable <IFacility> > GetFacilitiesByAccountId(long accountId)
{
var sqlQuery = new JoinSqlBuilder <TFacility, TClientFacility>(this.Connection.DialectProvider)
.Join <TClientFacility, TFacility>(i => i.FacilityId, i => i.Id)
.SelectAll <TFacility>()
.Where <TClientFacility>(ri => ri.ClientId == accountId)
.ToSql();
var result = await this.Connection.QueryAsync <TFacility>(sqlQuery, new { p_0 = accountId });
return(result);
}
public List <T> GetByFilter(BaseFilter filter)
{
using (var unitOfWork = UnitOfWorkProvider.GetUnitOfWork())
{
var builder = new JoinSqlBuilder <T, T>();
AddFilterOption(builder);
return(unitOfWork.DB.Select <T>(builder.ToSql()));
}
}
public async Task <IEnumerable <IRequestType> > GetRequestTypeByProcessId(long processId)
{
var sqlQuery = new JoinSqlBuilder <TRequestType, TProcessRequestType>(this.Connection.DialectProvider)
.Join <TProcessRequestType, TRequestType>(i => i.RequestTypeId, i => i.Id)
.SelectAll <TRequestType>()
.Where <TProcessRequestType>(ri => ri.ProcessId == processId)
.ToSql();
var result = await this.Connection.QueryAsync <TRequestType>(sqlQuery, new { p_0 = processId });
return(result);
}
public void DoubleWhereLeftJoinTest ()
{
var joinQuery = new JoinSqlBuilder<User, User>().LeftJoin<User, WithAliasAddress>(x => x.Id, x => x.UserId
, sourceWhere: x => x.Age > 18
, destinationWhere: x => x.Country == "Italy").ToSql ();
var expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Addresses\" ON \"User\".\"Id\" = \"Addresses\".\"UserId\" \nWHERE (\"User\".\"Age\" > 18) AND (\"Addresses\".\"Countryalias\" = 'Italy') \n".NormalizeSql();
var expectedNq = "SELECT \"User\".Id,\"User\".Name,\"User\".Age \nFROM \"User\" \n LEFT OUTER JOIN Addresses ON \"User\".Id = Addresses.UserId \nWHERE (\"User\".Age > 18) AND (Addresses.Countryalias = 'Italy') \n".NormalizeSql();
Assert.That(joinQuery.NormalizeSql(), Is.EqualTo(expected).Or.EqualTo(expectedNq));
var stmt = OrmLiteConfig.DialectProvider.ToSelectStatement(typeof(User), joinQuery);
Assert.That(Regex.Matches(stmt, @"(\b|\n)FROM(\b|\n)", RegexOptions.IgnoreCase).Count, Is.EqualTo(1));
}
public void DoubleWhereLeftJoinTest()
{
var joinQuery = new JoinSqlBuilder <User, User>().LeftJoin <User, WithAliasAddress>(x => x.Id, x => x.UserId
, sourceWhere: x => x.Age > 18
, destinationWhere: x => x.Country == "Italy").ToSql();
var expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Addresses\" ON \"User\".\"Id\" = \"Addresses\".\"UserId\" \nWHERE (\"User\".\"Age\" > 18) AND (\"Addresses\".\"Countryalias\" = 'Italy') \n".NormalizeSql();
var expectedNq = "SELECT \"User\".Id,\"User\".Name,\"User\".Age \nFROM \"User\" \n LEFT OUTER JOIN Addresses ON \"User\".Id = Addresses.UserId \nWHERE (\"User\".Age > 18) AND (Addresses.Countryalias = 'Italy') \n".NormalizeSql();
Assert.That(joinQuery.NormalizeSql(), Is.EqualTo(expected).Or.EqualTo(expectedNq));
var stmt = OrmLiteConfig.DialectProvider.ToSelectStatement(typeof(User), joinQuery);
Assert.That(Regex.Matches(stmt, @"(\b|\n)FROM(\b|\n)", RegexOptions.IgnoreCase).Count, Is.EqualTo(1));
}
public ActionResult List(int country_id)
{
//// get all template by country code
JoinSqlBuilder <SMSTemplateModel, SMSTemplateModel> jn = new JoinSqlBuilder <SMSTemplateModel, SMSTemplateModel>();
jn = jn.Join <SMSTemplateModel, Country>(m => m.CountryCode, k => k.Code);
jn.Where <Country>(m => m.Id == country_id);
var sql = jn.ToSql();
var templates = Db.Select <SMSTemplateModel>(sql);
var list_users = Cache_GetAllUsers();
//var countries = Cache_GetAllCountry();
foreach (var x in templates)
{
var z = list_users.Where(m => m.Id == x.CreatedBy);
if (z.Count() > 0)
{
var k = z.First();
if (string.IsNullOrEmpty(k.FullName))
{
x.CreatedByUsername = k.UserName;
}
else
{
x.CreatedByUsername = k.FullName;
}
}
else
{
x.CreatedByUsername = "******";
}
////
//var z1 = countries.Where(y => y.Code == x.CountryCode).FirstOrDefault();
//if (z1 != null)
//{
// x.CountryName = z1.Name;
//}
//else
//{
// x.CountryName = "Unknown";
//}
}
// now we need to
return(PartialView("_List", templates));
}
public static void Test(string connectionString)
{
//using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbConnection db = connectionString.OpenDbConnection())
{
db.CreateTable<User_2>(true);
db.CreateTable<UserData_2>(true);
db.CreateTable<UserService_2>(true);
//Insert Test
db.Insert(new UserData_2 { Id = 5, DataValue = "Value-5" });
db.Insert(new UserData_2 { Id = 6, DataValue = "Value-6" });
db.Insert(new UserService_2 { Id = 8, ServiceName = "Value-8" });
db.Insert(new UserService_2 { Id = 9, ServiceName = "Value-9" });
var user2 = new User_2 { Id = 1, Name = "A", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 8 };
db.Insert(user2);
db.Insert(new User_2 { Id = 2, Name = "B", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 9 });
db.Insert(new User_2 { Id = 3, Name = "B", CreatedDate = DateTime.Now });
//Update Test
user2.CreatedDate = DateTime.Now;
db.Update<User_2>(user2,x=>x.Id == 1);
//Select Test
var rowsB = db.Select<User_2>("Name = {0}", "B");
var rowsB1 = db.Select<User_2>(user => user.Name == "B");
var rowsUData = db.Select<UserData_2>();
var rowsUServ = db.Select<UserService_2>();
var jn2 = new JoinSqlBuilder<User_2, User_2>();
jn2 = jn2.Join<User_2, UserData_2>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.DataValue})
.Join<User_2, UserService_2>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending<User_2>(x => x.Name)
.OrderBy<User_2>(x => x.Id)
.Select<User_2>(x => x.Id);
var sql2 = jn2.ToSql();
var items2 = db.Query<User_2>(sql2);
Console.WriteLine("Ignored Field Selected Items - {0}",items2.Count());
var item = db.FirstOrDefault<User_2>(sql2);
}
}
public async Task <string> UsedOrNotInWorkOrder(long id)
{
string returnValue = "";
var sqlQuery = new JoinSqlBuilder <TTicketStatus, IWorkOrder>(this.Connection.DialectProvider)
.Join <TTicketStatus, IWorkOrder>(i => i.Id, i => i.RequestTypeId)
.Select <IWorkOrder>(p => new { Id = p.Id })
.Where <IWorkOrder>(ri => ri.RequestTypeId == id)
.ToSql();
var result = await this.Connection.QueryFirstOrDefaultAsync <IWorkOrder>(sqlQuery, new { p_0 = id });
if (result != null)
{
returnValue = result.Id.ToString();
}
return(returnValue);
}
public async Task <string> UsedOrNotInClient(long id)
{
string returnValue = "";
var sqlQuery = new JoinSqlBuilder <TFacility, TClientFacility>(this.Connection.DialectProvider)
.Join <TFacility, TClientFacility>(i => i.Id, i => i.FacilityId)
.Join <TClientFacility, TClient>(i => i.ClientId, i => i.Id)
.Select <TClient>(p => new { Name = p.ClientName })
.Where <TClientFacility>(ri => ri.FacilityId == id)
.ToSql();
var result = await this.Connection.QueryFirstOrDefaultAsync <TClient>(sqlQuery, new { p_0 = id });
if (result != null)
{
returnValue = result.ClientName;
}
return(returnValue);
}
public async Task <string> UsedOrNotInProcess(long id)
{
string returnValue = "";
var sqlQuery = new JoinSqlBuilder <TRequestType, TProcessRequestType>(this.Connection.DialectProvider)
.Join <TRequestType, TProcessRequestType>(i => i.Id, i => i.RequestTypeId)
.Join <TProcessRequestType, TProcess>(i => i.ProcessId, i => i.Id)
.Select <TProcess>(p => new { Name = p.Name })
.Where <TProcessRequestType>(ri => ri.RequestTypeId == id)
.ToSql();
var result = await this.Connection.QueryFirstOrDefaultAsync <TProcess>(sqlQuery, new { p_0 = id });
if (result != null)
{
returnValue = result.Name;
}
return(returnValue);
}
public void FieldNameLeftJoinTest()
{
var joinQuery = new JoinSqlBuilder <User, User> ().LeftJoin <User, Address> (x => x.Id, x => x.UserId).ToSql();
var expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Address\" ON \"User\".\"Id\" = \"Address\".\"UserId\" \n";
Assert.AreEqual(expected, joinQuery);
joinQuery = new JoinSqlBuilder <WithAliasUser, WithAliasUser> ().LeftJoin <WithAliasUser, WithAliasAddress> (x => x.Id, x => x.UserId).ToSql();
expected = "SELECT \"Users\".\"Id\",\"Users\".\"Nickname\",\"Users\".\"Agealias\" \nFROM \"Users\" \n LEFT OUTER JOIN \"Addresses\" ON \"Users\".\"Id\" = \"Addresses\".\"UserId\" \n";
Assert.AreEqual(expected, joinQuery);
joinQuery = new JoinSqlBuilder <User, User> ().LeftJoin <User, WithAliasAddress> (x => x.Id, x => x.UserId).ToSql();
expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Addresses\" ON \"User\".\"Id\" = \"Addresses\".\"UserId\" \n";
Assert.AreEqual(expected, joinQuery);
}
public void JoinSqlBuilderWithFieldAliasTest()
{
var factory = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider);
using (var db = factory.OpenDbConnection())
{
var jn = new JoinSqlBuilder<Northwind.Common.DataModel.Employee, Northwind.Common.DataModel.EmployeeTerritory>();
jn = jn.Join<Northwind.Common.DataModel.Employee, Northwind.Common.DataModel.EmployeeTerritory>(x => x.Id, x => x.EmployeeId)
.LeftJoin<Northwind.Common.DataModel.EmployeeTerritory, Northwind.Common.DataModel.Territory>(x => x.TerritoryId, x => x.Id)
.Where<Northwind.Common.DataModel.Territory>(x => x.TerritoryDescription.Trim() == "Westboro");
var sql = jn.ToSql();
// here sql should contain Employees.EmployeID instead of Employees.Id
var result = db.Select<Northwind.Common.DataModel.Employee>(sql);
// the generated Sql is ok if the Query doesn't fail
}
}
public void FieldNameLeftJoinTest ()
{
var joinQuery = new JoinSqlBuilder<User, User> ().LeftJoin<User, Address> (x => x.Id, x => x.UserId).ToSql ();
var expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Address\" ON \"User\".\"Id\" = \"Address\".\"UserId\" \n";
Assert.AreEqual (expected, joinQuery);
joinQuery = new JoinSqlBuilder<WithAliasUser, WithAliasUser> ().LeftJoin<WithAliasUser, WithAliasAddress> (x => x.Id, x => x.UserId).ToSql ();
expected = "SELECT \"Users\".\"Id\",\"Users\".\"Nickname\",\"Users\".\"Agealias\" \nFROM \"Users\" \n LEFT OUTER JOIN \"Addresses\" ON \"Users\".\"Id\" = \"Addresses\".\"UserId\" \n";
Assert.AreEqual (expected, joinQuery);
joinQuery = new JoinSqlBuilder<User, User> ().LeftJoin<User, WithAliasAddress> (x => x.Id, x => x.UserId).ToSql ();
expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Addresses\" ON \"User\".\"Id\" = \"Addresses\".\"UserId\" \n";
Assert.AreEqual (expected, joinQuery);
}
public void JoinSqlBuilderWithFieldAliasTest()
{
var factory = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider);
using (var db = factory.OpenDbConnection())
{
var jn = new JoinSqlBuilder <Northwind.Common.DataModel.Employee, Northwind.Common.DataModel.EmployeeTerritory>();
jn = jn.Join <Northwind.Common.DataModel.Employee, Northwind.Common.DataModel.EmployeeTerritory>(x => x.Id, x => x.EmployeeId)
.LeftJoin <Northwind.Common.DataModel.EmployeeTerritory, Northwind.Common.DataModel.Territory>(x => x.TerritoryId, x => x.Id)
.Where <Northwind.Common.DataModel.Territory>(x => x.TerritoryDescription.Trim() == "Westboro");
var sql = jn.ToSql();
// here sql should contain Employees.EmployeID instead of Employees.Id
var result = db.Query <Northwind.Common.DataModel.Employee>(sql);
// the generated Sql is ok if the Query doesn't fail
}
}
public List <P2PBackup.Common.Node> GetAllHavingPlugin(int?groupId, string pluginName)
{
using (dbc = DAL.Instance.GetDb()){
if (groupId.HasValue)
{
var jn = new JoinSqlBuilder <Node, Plugin>();
jn = jn.Join <Node, Plugin>(x => x.Id, y => y.NodeId)
.Where <Node>(x => x.Group == groupId.Value)
.And <Plugin>(p => p.Name == pluginName);
// temp hack waiting for issue #256 to be solved
jn = jn.SelectAll <Node>();
Console.WriteLine("GetAllHavingPlugin : sql=" + jn.ToSql());
return(dbc.Query <Node>(jn.ToSql()));
}
else
{
return(dbc.Select <P2PBackup.Common.Node>());
}
}
}
public async Task <IWorkOrder> FindWorkOrderById(long id)
{
// var workOrder = entity as TWorkOrder;
var sqlQuery = new JoinSqlBuilder <TUser, TWorkOrder>(this.Connection.DialectProvider)
.LeftJoin <TUser, TWorkOrder>(i => i.Id, i => i.AssignedUserId)
.SelectAll <TWorkOrder>()
.Select <TUser>(p => new { CoordinatorId = p.CoordinatorId })
.Where <TWorkOrder>(ri => ri.Id == id)
.ToSql();
var result = await this.Connection.QueryFirstAsync <TWorkOrder>(sqlQuery, new { p_0 = id });
IEnumerable <IWorkOrderClarification> workOrderClarifications = await this._workOrderClarificationRepository.FindClarificationsByWorkOrderId(id);
if (result != null)
{
result.WorkOrderClarifications = workOrderClarifications;
}
return(result);
}
public void FieldNameLeftJoinTest ()
{
var joinQuery = new JoinSqlBuilder<User, User> ().LeftJoin<User, Address> (x => x.Id, x => x.UserId).ToSql ();
var expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Address\" ON \"User\".\"Id\" = \"Address\".\"UserId\" \n".NormalizeSql();
var expectedNq = "SELECT \"User\".Id,\"User\".Name,\"User\".Age \nFROM \"User\" \n LEFT OUTER JOIN Address ON \"User\".Id = Address.UserId \n".NormalizeSql();
Assert.That(joinQuery.NormalizeSql(), Is.EqualTo(expected).Or.EqualTo(expectedNq));
joinQuery = new JoinSqlBuilder<WithAliasUser, WithAliasUser> ().LeftJoin<WithAliasUser, WithAliasAddress> (x => x.Id, x => x.UserId).ToSql ();
expected = "SELECT \"Users\".\"Id\",\"Users\".\"Nickname\",\"Users\".\"Agealias\" \nFROM \"Users\" \n LEFT OUTER JOIN \"Addresses\" ON \"Users\".\"Id\" = \"Addresses\".\"UserId\" \n".NormalizeSql();
expectedNq = "SELECT Users.Id,Users.Nickname,Users.Agealias \nFROM Users \n LEFT OUTER JOIN Addresses ON Users.Id = Addresses.UserId \n".NormalizeSql();
Assert.That(joinQuery.NormalizeSql(), Is.EqualTo(expected).Or.EqualTo(expectedNq));
joinQuery = new JoinSqlBuilder<User, User> ().LeftJoin<User, WithAliasAddress> (x => x.Id, x => x.UserId).ToSql ();
expected = "SELECT \"User\".\"Id\",\"User\".\"Name\",\"User\".\"Age\" \nFROM \"User\" \n LEFT OUTER JOIN \"Addresses\" ON \"User\".\"Id\" = \"Addresses\".\"UserId\" \n".NormalizeSql();
expectedNq = "SELECT \"User\".Id,\"User\".Name,\"User\".Age \nFROM \"User\" \n LEFT OUTER JOIN Addresses ON \"User\".Id = Addresses.UserId \n".NormalizeSql();
Assert.That(joinQuery.NormalizeSql(), Is.EqualTo(expected).Or.EqualTo(expectedNq));
}
public JsonResult GetDMHCByRole(string role)
{
List<DanhMuc_HanhChinh> data = new List<DanhMuc_HanhChinh>();
string ma_hc = CurrentUser.MaHC != null ? CurrentUser.MaHC : "";
List<ListModel> roles_lower = this.GetLowerRoles((RoleEnum)Enum.Parse(typeof(RoleEnum), CurrentUser.Roles[0]));
if (roles_lower.Select(x => x.Id).Contains(role))
{
JoinSqlBuilder<DanhMuc_HanhChinh, DanhMuc_HanhChinh> jn = new JoinSqlBuilder<DanhMuc_HanhChinh, DanhMuc_HanhChinh>();
jn.And<DanhMuc_HanhChinh>(x => ((RoleEnum)Enum.Parse(typeof(RoleEnum), CurrentUser.Roles[0]) == RoleEnum.Admin || x.MaHC.StartsWith(ma_hc)));
SqlExpressionVisitor<DanhMuc_HanhChinh> sql_exp = Db.CreateExpression<DanhMuc_HanhChinh>();
string st = jn.ToSql();
int idx = st.IndexOf("WHERE");
sql_exp.SelectExpression = st.Substring(0, idx);
sql_exp.WhereExpression = string.Format("{0} AND LEN([MaHC]) = {1}", st.Substring(idx), this.GetLenMaHCByRole((RoleEnum)Enum.Parse(typeof(RoleEnum), role)));
string sql = sql_exp.ToSelectStatement();
data = Db.Select<DanhMuc_HanhChinh>(sql);
}
return Json(new {
Data = data
});
}
public static void Test()
{
OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
var path = GetFileConnectionString();
if (File.Exists(path))
File.Delete(path);
//using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbConnection db = path.OpenDbConnection())
{
db.CreateTable<User>(true);
db.CreateTable<UserData>(true);
db.CreateTable<UserService>(true);
db.Insert(new UserData { Id = 5, UserDataValue = "Value-5" });
db.Insert(new UserData { Id = 6, UserDataValue = "Value-6" });
db.Insert(new UserService { Id = 8, ServiceName = "Value-8" });
db.Insert(new UserService { Id = 9, ServiceName = "Value-9" });
db.Insert(new User { Id = 1, Name = "A", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 8 });
db.Insert(new User { Id = 2, Name = "B", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 9 });
db.Insert(new User { Id = 3, Name = "B", CreatedDate = DateTime.Now });
var rowsB = db.Select<User>("Name = {0}", "B");
var rowsB1 = db.Select<User>(user => user.Name == "B");
var jn = new JoinSqlBuilder<UserEx, User>();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.UserDataValue })
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending<User>(x=>x.Name)
.OrderBy<User>(x=>x.Id)
.Select<User>(x=>x.Id)
.Where<User>(x=> x.Id == 0);
var sql = jn.ToSql();
var items = db.Query<UserEx>(sql);
jn.Clear();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending<User>(x => x.Name)
.OrderBy<User>(x => x.Id)
.OrderByDescending<UserService>(x => x.ServiceName)
.Where<User>(x => x.Id > 0)
.Or<User>(x => x.Id < 10)
.And<User>(x => x.Name != "" || x.Name != null);
var sql2 = jn.ToSql();
var item = db.QuerySingle<UserEx>(sql2);
jn.Clear();
jn = new JoinSqlBuilder<UserEx, User>();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending<User>(x=>x.Name)
.OrderBy<User>(x=>x.Id)
.SelectAll<UserData>()
.Where<User>(x=> x.Id == 0);
var sql3 = jn.ToSql();
var items3 = db.Query<UserEx>(sql3);
jn.Clear();
jn = new JoinSqlBuilder<UserEx, User>();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.UserDataValue })
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending<User>(x=>x.Name)
.OrderBy<User>(x=>x.Id)
.SelectDistinct()
.SelectAll<UserData>()
.Where<User>(x=> x.Id == 0);
var sql4 = jn.ToSql();
var items4 = db.Query<UserEx>(sql4);
jn.Clear();
jn = new JoinSqlBuilder<UserEx, User>();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending<User>(x=>x.Name)
.OrderBy<User>(x=>x.Id)
.SelectCount<User>(x=>x.Id)
.Where<User>(x=> x.Id == 0);
var sql5 = jn.ToSql();
var items5 = db.GetScalar<long>(sql5);
jn.Clear();
jn = new JoinSqlBuilder<UserEx, User>();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending<User>(x => x.Name)
.OrderBy<User>(x=>x.Id)
.SelectMax<User>(x=>x.Id)
.Where<User>(x=> x.Id == 0);
var sql6 = jn.ToSql();
var items6 = db.GetScalar<long>(sql6);
jn.Clear();
jn = new JoinSqlBuilder<UserEx, User>();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending<User>(x => x.Name)
.OrderBy<User>(x=>x.Id)
.SelectMin<User>(x=>x.Id)
.Where<User>(x=> x.Id == 0);
var sql7 = jn.ToSql();
var items7 = db.GetScalar<long>(sql7);
jn.Clear();
jn = new JoinSqlBuilder<UserEx, User>();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending<User>(x => x.Name)
.OrderBy<User>(x=>x.Id)
.SelectAverage<User>(x=>x.Id)
.Where<User>(x=> x.Id == 0);
var sql8 = jn.ToSql();
var items8 = db.GetScalar<long>(sql8);
jn.Clear();
jn = new JoinSqlBuilder<UserEx, User>();
jn = jn.Join<User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin<User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending<User>(x => x.Name)
.OrderBy<User>(x=>x.Id)
.SelectSum<User>(x=>x.Id)
.Where<User>(x=> x.Id == 0);
var sql9 = jn.ToSql();
var items9 = db.GetScalar<long>(sql9);
}
File.Delete(path);
}
public ActionResult Listings(string p_pro, string p_type, string p_status1, string p_status2, string p_search, int?p_page)
{
List <ListingProperty> model = new List <ListingProperty>();
JoinSqlBuilder <ListingProperty, ListingProperty> jn = new JoinSqlBuilder <ListingProperty, ListingProperty>();
jn = jn.LeftJoin <ListingProperty, ListingCategory>(x => x.Assign_Category, y => y.Id);
jn = jn.LeftJoin <ListingProperty, ListingPropertyType>(x => x.Assign_Type, y => y.Id);
if (p_pro != null && p_search == null)
{
var pro = Db.Where <ListingCategory>(x => (x.SEO == p_pro && x.IsActive));
if (pro != null && pro.Count != 0)
{
ViewData["Title"] = pro.TakeFirst().Name;
jn = jn.Where <ListingCategory>(x => (x.Id == pro.TakeFirst().Id));
}
else
{
throw new HttpException(404, "Category listing not found!", 0);
}
}
else if (p_type != null && p_search == null)
{
var type = Db.Where <ListingPropertyType>(x => (x.SEO == p_type && x.IsActive));
if (type != null && type.Count != 0)
{
ViewData["Title"] = type.TakeFirst().Name;
jn = jn.Where <ListingCategory>(x => (x.Id == type.TakeFirst().Id));
}
else
{
throw new HttpException(404, "Type listing not found!", 0);
}
}
else if (p_status1 != null && p_search == null)
{
int result = 0;
foreach (var e in (Status1Enum[])Enum.GetValues(typeof(Status1Enum)) ?? Enumerable.Empty <Status1Enum>())
{
if (e.ToString().ToLower() == p_status1.ToLower())
{
result = (int)e;
break;
}
}
if (result != 0)
{
ViewData["Title"] = ((Status1Enum)result).ToString();
jn = jn.Where <ListingProperty>(x => (x.Status1 == result));
}
else
{
throw new HttpException(404, "Status 1 listing not found!", 0);
}
}
else if (p_status2 != null && p_search == null)
{
int result = 0;
foreach (var e in (Status2Enum[])Enum.GetValues(typeof(Status2Enum)) ?? Enumerable.Empty <Status2Enum>())
{
if (e.ToString().ToLower() == p_status2.ToLower())
{
result = (int)e;
break;
}
}
if (result != 0)
{
ViewData["Title"] = ((Status2Enum)result).ToString();
jn = jn.Where <ListingProperty>(x => (x.Status2 == result));
}
else
{
throw new HttpException(404, "Status 2 listing not found!", 0);
}
}
else if (p_search != null && p_pro != null)
{
ViewData["Title"] = "Search result";
/* TODO */
}
else if (p_search != null /* && p_pro == null */)
{
ViewData["Title"] = "Search result";
jn = jn.Where <ListingProperty>(x => (x.Info_Title.Contains(p_search)));
}
else
{
ViewData["Title"] = "All Properties";
}
jn = jn.Where <ListingProperty>(x => (x.IsActive && (x.IsSchedule != true || (x.IsSchedule && x.PublishSchedule <= DateTime.Now && x.UnPublishSchedule >= DateTime.Now))));
var sql = jn.ToSql();
var jn_count = jn.SelectCount <ListingProperty>(m => m.Id);
var count = Db.Scalar <int>(jn_count.ToSql());
var pages = (int)Math.Ceiling((decimal)count / (decimal)item_per_page);
var current_page = 1;
if (p_page.HasValue)
{
current_page = p_page.Value;
}
if (current_page > pages && pages > 0)
{
current_page = pages;
}
var start_index = (current_page - 1) * item_per_page;
model = Db.Select <ListingProperty>(sql).ToList(); /*model = Db.Select<ListingProperty>(sql).Skip(start_index).Take(item_per_page).ToList();*/
ViewData["page_curr"] = current_page;
ViewData["page_total"] = pages;
return(View("ListingList", model));
}
public List <BackupSetSchedule> GetPlan(DateTime from, DateTime to)
{
//return GetNextToSchedule((int)to.Subtract(DateTime.Now).TotalMinutes);
int interval = (int)to.Subtract(DateTime.Now).TotalMinutes;
Console.WriteLine(" -- - - - GetPlan(): wanted interval (minutes)=" + interval);
DateTime now = DateTime.Now;
DateTime endInterval = DateTime.Now.AddMinutes(interval);
using (dbc = DAL.Instance.GetDb()){
SqlExpressionVisitor <ScheduleTime> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor <ScheduleTime>();
ev.Where(st => st.Day == DayOfWeek.Friday);
var jn = new JoinSqlBuilder <BackupSetSchedule, BackupSet>();
//string endperiod = DateTime.Now.AddMinutes(interval).Hour+":"+DateTime.Now.AddMinutes(interval).Minute;
// 1 - gather what to schedule for the rest of the current hour
jn = jn.LeftJoin <BackupSet, ScheduleTime>(x => x.Id, y => y.BackupSetId)
.Where <BackupSet>(x => x.Enabled && !x.IsTemplate)
.And <ScheduleTime>(y => y.Day == now.DayOfWeek &&
y.BeginHour == now.Hour &&
y.BeginMinute >= now.Minute
/*y.Day == endInterval.DayOfWeek*/);
// 2 -remaining hours of the current day
if (endInterval.DayOfWeek == now.DayOfWeek && endInterval.Hour > now.Hour + 1)
{
jn.Or <BackupSet>(x => x.Enabled && !x.IsTemplate)
.And <ScheduleTime>(s => s.Day == now.DayOfWeek &&
s.BeginHour > now.Hour &&
s.BeginHour < endInterval.Hour);
/*jn = jn.Or<ScheduleTime>(s => s.BeginHour == endInterval.Hour
* && s.BeginMinute < endInterval.Minute);*/
}
else if (endInterval.DayOfWeek != now.DayOfWeek)
{
jn.Or <BackupSet>(x => x.Enabled && !x.IsTemplate)
.And <ScheduleTime>(s => s.Day == now.DayOfWeek &&
s.BeginHour > now.Hour &&
s.BeginHour <= 23);
}
// 3- loop and add every complete (24h) day until (interval_end_day -1), if any.
int nbOfDays = endInterval.Subtract(now).Days;
for (int i = 1; i <= nbOfDays; i++)
{
jn.Or <BackupSet>(x => x.Enabled && !x.IsTemplate)
.And <ScheduleTime>(s => s.Day == now.AddDays(i).DayOfWeek);
}
if (endInterval.DayOfWeek != now.DayOfWeek)
{
// remaining hours of the end interval day
jn.Or <BackupSet>(x => x.Enabled && !x.IsTemplate)
.And <ScheduleTime>(y => y.Day == endInterval.DayOfWeek &&
y.BeginHour < endInterval.Hour
//&& y.BeginMinute < endInterval.Minute
);
// remaining minutes
jn.Or <BackupSet>(x => x.Enabled && !x.IsTemplate)
.And <ScheduleTime>(y => y.Day == endInterval.DayOfWeek &&
y.BeginHour == endInterval.Hour &&
y.BeginMinute < endInterval.Minute
);
}
jn.SelectAll <BackupSetSchedule>();
Console.WriteLine("next to schedule SQL = " + jn.ToSql());
return(dbc.Query <BackupSetSchedule>(jn.ToSql()));
}
}
public void SelectCountTest()
{
OrmLiteConfig.DialectProvider = SqliteDialect.Provider;
var joinQuery =
new JoinSqlBuilder<User, User>().LeftJoin<User, Address>(x => x.Id, x => x.UserId)
.SelectCount<User>(x => x.Id).ToSql();
var expected =
"SELECT COUNT(\"User\".\"Id\") \nFROM \"User\" \n LEFT OUTER JOIN \"Address\" ON \"User\".\"Id\" = \"Address\".\"UserId\" \n";
Assert.AreEqual(expected, joinQuery);
}
protected virtual void AddFilterOption(JoinSqlBuilder <T, T> builder)
{
}
public static void Test(string connectionString)
{
//using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbConnection db = connectionString.OpenDbConnection())
{
db.CreateTable <User>(true);
db.CreateTable <UserData>(true);
db.CreateTable <UserService>(true);
db.Insert(new UserData {
Id = 5, UserDataValue = "Value-5"
});
db.Insert(new UserData {
Id = 6, UserDataValue = "Value-6"
});
db.Insert(new UserService {
Id = 8, ServiceName = "Value-8"
});
db.Insert(new UserService {
Id = 9, ServiceName = "Value-9"
});
db.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 8
});
db.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 9
});
db.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var rowsB = db.Select <User>("Name = {0}", "B");
var rowsB1 = db.Select <User>(user => user.Name == "B");
var jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.UserDataValue })
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.Select <User>(x => x.Id)
.Where <User>(x => x.Id == 0);
var sql = jn.ToSql();
var items = db.Query <UserEx>(sql);
jn.Clear();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.OrderByDescending <UserService>(x => x.ServiceName)
.Where <User>(x => x.Id > 0)
.Or <User>(x => x.Id < 10)
.And <User>(x => x.Name != "" || x.Name != null);
var sql2 = jn.ToSql();
var item = db.QuerySingle <UserEx>(sql2);
jn.Clear();
jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.SelectAll <UserData>()
.Where <User>(x => x.Id == 0);
var sql3 = jn.ToSql();
var items3 = db.Query <UserEx>(sql3);
jn.Clear();
jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.UserDataValue })
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.SelectDistinct()
.SelectAll <UserData>()
.Where <User>(x => x.Id == 0);
var sql4 = jn.ToSql();
var items4 = db.Query <UserEx>(sql4);
jn.Clear();
jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.SelectCount <User>(x => x.Id)
.Where <User>(x => x.Id == 0);
var sql5 = jn.ToSql();
var items5 = db.GetScalar <long>(sql5);
jn.Clear();
jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.SelectMax <User>(x => x.Id)
.Where <User>(x => x.Id == 0);
var sql6 = jn.ToSql();
var items6 = db.GetScalar <long>(sql6);
jn.Clear();
jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.SelectMin <User>(x => x.Id)
.Where <User>(x => x.Id == 0);
var sql7 = jn.ToSql();
var items7 = db.GetScalar <long>(sql7);
jn.Clear();
jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.SelectAverage <User>(x => x.Id)
.Where <User>(x => x.Id == 0);
var sql8 = jn.ToSql();
var items8 = db.GetScalar <long>(sql8);
jn.Clear();
jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.SelectSum <User>(x => x.Id)
.Where <User>(x => x.Id == 0);
var sql9 = jn.ToSql();
var items9 = db.GetScalar <long>(sql9);
}
}
public static void Test()
{
OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
var path = GetFileConnectionString();
if (File.Exists(path))
File.Delete(path);
//using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbConnection db = path.OpenDbConnection())
{
db.CreateTable<User_2>(true);
db.CreateTable<UserData_2>(true);
db.CreateTable<UserService_2>(true);
//Insert Test
db.Insert(new UserData_2 { Id = 5, DataValue = "Value-5" });
db.Insert(new UserData_2 { Id = 6, DataValue = "Value-6" });
db.Insert(new UserService_2 { Id = 8, ServiceName = "Value-8" });
db.Insert(new UserService_2 { Id = 9, ServiceName = "Value-9" });
var user2 = new User_2 { Id = 1, Name = "A", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 8 };
db.Insert(user2);
db.Insert(new User_2 { Id = 2, Name = "B", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 9 });
db.Insert(new User_2 { Id = 3, Name = "B", CreatedDate = DateTime.Now });
//Update Test
user2.CreatedDate = DateTime.Now;
db.Update<User_2>(user2,x=>x.Id == 1);
//Select Test
var rowsB = db.SelectFmt<User_2>("Name = {0}", "B");
var rowsB1 = db.Select<User_2>(user => user.Name == "B");
var rowsUData = db.Select<UserData_2>();
var rowsUServ = db.Select<UserService_2>();
var jn2 = new JoinSqlBuilder<User_2, User_2>();
jn2 = jn2.Join<User_2, UserData_2>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.DataValue})
.Join<User_2, UserService_2>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending<User_2>(x => x.Name)
.OrderBy<User_2>(x => x.Id)
.Select<User_2>(x => x.Id);
var sql2 = jn2.ToSql();
var items2 = db.Select<User_2>(sql2);
Console.WriteLine("Ignored Field Selected Items - {0}",items2.Count());
var item = db.Single<User_2>(sql2);
}
File.Delete(path);
}
public static void Test()
{
OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
var path = GetFileConnectionString();
if (File.Exists(path))
{
File.Delete(path);
}
//using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbConnection db = path.OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.CreateTable <User>(true);
dbCmd.CreateTable <UserData>(true);
dbCmd.CreateTable <UserService>(true);
dbCmd.Insert(new UserData {
Id = 5, UserDataValue = "Value-5"
});
dbCmd.Insert(new UserData {
Id = 6, UserDataValue = "Value-6"
});
dbCmd.Insert(new UserService {
Id = 8, ServiceName = "Value-8"
});
dbCmd.Insert(new UserService {
Id = 9, ServiceName = "Value-9"
});
dbCmd.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 8
});
dbCmd.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 9
});
dbCmd.Insert(new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
var rowsB = dbCmd.Select <User>("Name = {0}", "B");
var rowsB1 = dbCmd.Select <User>(user => user.Name == "B");
var jn = new JoinSqlBuilder <UserEx, User>();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.UserDataValue })
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.Select <User>(x => x.Id)
.Where <User>(x => x.Id == 0);
var sql = jn.ToSql();
var items = db.Query <UserEx>(sql);
jn.Clear();
jn = jn.Join <User, UserData>(x => x.UserDataId, x => x.Id)
.LeftJoin <User, UserService>(x => x.UserServiceId, x => x.Id)
.OrderByDescending <User>(x => x.Name)
.OrderBy <User>(x => x.Id)
.OrderByDescending <UserService>(x => x.ServiceName)
.Where <User>(x => x.Id > 0)
.Or <User>(x => x.Id < 10)
.And <User>(x => x.Name != "" || x.Name != null);
var sql2 = jn.ToSql();
var item = db.QuerySingle <UserEx>(sql2);
}
File.Delete(path);
}
public void Can_execute_JoinSqlBuilder_as_SqlExpression()
{
if (OrmLiteConfig.UseParameterizeSqlExpressions) return;
var joinQuery = new JoinSqlBuilder<User, User>()
.LeftJoin<User, WithAliasAddress>(x => x.Id, x => x.UserId
, sourceWhere: x => x.Age > 18
, destinationWhere: x => x.Country == "Italy");
using (var db = OpenDbConnection())
{
db.DropAndCreateTable<User>();
db.DropAndCreateTable<WithAliasAddress>();
var userId = db.Insert(new User { Age = 27, Name = "Foo" }, selectIdentity:true);
db.Insert(new WithAliasAddress { City = "Rome", Country = "Italy", UserId = (int)userId });
var results = db.Select<User>(joinQuery);
Assert.That(results.Count, Is.EqualTo(1));
}
}
protected override void AddFilterOption(JoinSqlBuilder <User, User> builder)
{
}
/// <summary>Tests this object.</summary>
public static void Test()
{
OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
var path = GetFileConnectionString();
if (File.Exists(path))
{
File.Delete(path);
}
//using (IDbConnection db = ":memory:".OpenDbConnection())
using (IDbConnection db = path.OpenDbConnection())
{
db.CreateTable <User_2>(true);
db.CreateTable <UserData_2>(true);
db.CreateTable <UserService_2>(true);
//Insert Test
db.Insert(new UserData_2 {
Id = 5, DataValue = "Value-5"
});
db.Insert(new UserData_2 {
Id = 6, DataValue = "Value-6"
});
db.Insert(new UserService_2 {
Id = 8, ServiceName = "Value-8"
});
db.Insert(new UserService_2 {
Id = 9, ServiceName = "Value-9"
});
var user2 = new User_2 {
Id = 1, Name = "A", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 8
};
db.Insert(user2);
db.Insert(new User_2 {
Id = 2, Name = "B", CreatedDate = DateTime.Now, UserDataId = 5, UserServiceId = 9
});
db.Insert(new User_2 {
Id = 3, Name = "B", CreatedDate = DateTime.Now
});
//Update Test
user2.CreatedDate = DateTime.Now;
db.Update <User_2>(user2, x => x.Id == 1);
//Select Test
var rowsB = db.Select <User_2>("Name = {0}", "B");
var rowsB1 = db.Select <User_2>(user => user.Name == "B");
var rowsUData = db.Select <UserData_2>();
var rowsUServ = db.Select <UserService_2>();
var jn2 = new JoinSqlBuilder <User_2, User_2>();
jn2 = jn2.Join <User_2, UserData_2>(x => x.UserDataId, x => x.Id, x => new { x.Name, x.Id }, x => new { x.DataValue })
.Join <User_2, UserService_2>(x => x.UserServiceId, x => x.Id, null, x => new { x.ServiceName })
.OrderByDescending <User_2>(x => x.Name)
.OrderBy <User_2>(x => x.Id)
.Select <User_2>(x => x.Id);
var sql2 = jn2.ToSql();
var items2 = db.Query <User_2>(sql2);
Console.WriteLine("Ignored Field Selected Items - {0}", items2.Count());
var item = db.FirstOrDefault <User_2>(sql2);
}
File.Delete(path);
}
public void ComplexJoin_with_JoinSqlBuilder()
{
using (var db = OpenDbConnection())
{
InitTables(db);
/* This gives the expected values for BazId */
var jn = new JoinSqlBuilder<JoinResult, FooBar>()
.Join<FooBar, BarJoin>(
sourceColumn: dp => dp.BarId,
destinationColumn: p => p.Id,
destinationTableColumnSelection: p => new { BarName = p.Name, BarId = p.Id })
.Join<FooBar, FooBarBaz>(
sourceColumn: dp => dp.Id,
destinationColumn: dpss => dpss.FooBarId,
destinationTableColumnSelection: dpss => new { dpss.Amount, FooBarBazId = dpss.Id });
jn.Join<FooBarBaz, Baz>(
sourceColumn: dpss => dpss.BazId,
destinationColumn: ss => ss.Id,
destinationTableColumnSelection: ss => new { BazId = ss.Id, BazName = ss.Name });
jn.Select<FooBar>(dp => new { Id = dp.Id, });
var results = db.Select<JoinResult>(jn.ToSql());
db.GetLastSql().Print();
results.PrintDump();
var fooBarBaz = results.First(x => x.FooBarBazId == _fooBarBaz1Id);
Assert.That(fooBarBaz.BazId, Is.EqualTo(_baz2Id));
fooBarBaz = results.First(x => x.FooBarBazId == _fooBarBaz2Id);
Assert.That(fooBarBaz.BazId, Is.EqualTo(_baz1Id));
fooBarBaz = results.First(x => x.FooBarBazId == _fooBarBaz2Id);
Assert.That(fooBarBaz.BazId, Is.EqualTo(_baz1Id));
}
}
