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)); } }