public void Test_ListQuery_Streamed_Apply()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Order = new List <SortField>()
{
new SortField()
{
Name = "FirstName"
}
};
queryDef.Offset = 0;
queryDef.Limit = 50;
var qb = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.UseStreamingResult()
.Apply();
Assert.AreEqual(0, qb.PagingOptions.Value.Offset);
Assert.AreEqual(50, qb.PagingOptions.Value.Limit);
}
public void Test_ListQuery_Paging()
{
var expectedSql = "SELECT COUNT(1) FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p0);\r\nSELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p1) ORDER BY [FirstName] ASC OFFSET 0 ROWS FETCH NEXT 26 ROWS ONLY;";
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Order = new List <SortField>()
{
new SortField()
{
Name = "FirstName"
}
};
queryDef.Filters = new List <ListFilter>()
{
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
}
};
queryDef.Limit = 25;
queryDef.Offset = 0;
var stmt = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText.Trim(), true);
}
public static async Task <object> GetUserApplications(ApplicationDbContext appDb, string userId, Dictionary <string, int> page, Dictionary <string, string> filter, string sort, IQueryCollection query)
{
ListQueryStrategy strategy = new ListQueryBuilder("Name").Build(page, filter, sort, query);
var appsQuery = appDb.Applications.Where(f => f.Organizations.Count == 0 || f.Organizations.Any(g => appDb.Users.Where(h => h.Id == userId).SelectMany(h => h.CustomOrganizations).Select(h => h.OrganizationId).Contains(g.OrganizationId)))
.Select(a => new
{
_Type = "applications",
Id = a.Id,
Name = a.Name,
Url = a.Url,
Description = a.Description,
Logo = a.Logo
});
return(await strategy.Run(
appsQuery,
q => f => f.Name.Contains(q),
f => new
{
Type = f._Type,
Id = f.Id,
//Meta = new
//{
// Created = f.Created
//},
Attributes = new
{
Name = f.Name,
Description = f.Description,
Url = f.Url,
Logo = f.Logo
}
},
null));
}
public async Task <ActionResult> ListUserGroups(string userId, Dictionary <string, int> page, Dictionary <string, string> filter, string sort)
{
ListQueryStrategy strategy = new ListQueryBuilder("DisplayName").Build(page, filter, sort, Request.Query);
//var query = appDb.UserRoles.Select(f => new { User = f.User, Role = f.Role }).SelectMany(f => f.Role.Ancestors.Select(g => new { UserId = f.User.Id, IsIn = g.ParentId, BecauseOf = g.ChildId, Nested = 1 }));
var query = (from m in appDb.UserRoles
join link in appDb.Roles.SelectMany(f => f.Ancestors) on m.RoleId equals link.ChildId
select new
{
UserId = m.UserId,
IsIn = link.ParentId,
DisplayName = link.Parent.Name,
BecauseOf = m.RoleId,
BecauseName = m.Role.Name,
Nested = 1
}).Union(from m in appDb.UserRoles
select new
{
UserId = m.UserId,
IsIn = m.RoleId,
DisplayName = m.Role.Name,
BecauseOf = m.RoleId,
BecauseName = m.Role.Name,
Nested = 0
})
.Where(f => f.UserId == userId)
.OrderBy(f => f.BecauseName).ThenBy(f => f.Nested).ThenBy(f => f.DisplayName);
var list = await query.ToListAsync();
return(Json(await strategy.Run(
query,
q => f => f.DisplayName.Contains(q),
f => new
{
Type = "groups",
Id = f.IsIn,
Attributes = new
{
DisplayName = f.DisplayName
},
Relationships = new GroupRelationshipsModel
{
Parent = new GroupParentModel
{
Data = new JsonApiResourceId("groups", f.BecauseOf)
}
}
},
row =>
{
row.Relationships.Parent = row.Relationships.Parent.Data.Id == row.Id ? null : row.Relationships.Parent;
})));
}
public void Test_ListQuery_DefaultLimit()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
var qb = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.BeforeApply((config) => config.UseLimitOracle = false)
.DefaultLimit(150)
.Apply();
Assert.AreEqual(150, qb.PagingOptions.Value.Limit);
}
public void Test_ListQuery_DefaultSort()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
var qb = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.DefaultSort("FirstName")
.Apply();
Assert.AreEqual(1, qb.OrderBy.Count);
Assert.AreEqual("FirstName", qb.OrderBy.First().ColumnName);
}
public async Task <ActionResult> ListGroups(Dictionary <string, int> page, Dictionary <string, string> filter, string sort)
{
ListQueryStrategy mods = new ListQueryBuilder("Name").Build(page, filter, sort, Request.Query);
return(Json(await mods.Run(
from u in appDb.Roles
select new
{
_Type = "groups",
Id = u.Id,
Name = u.Name,
Description = u.Description
//Created = u.Created,
//MemberId = u.MemberId,
//Name = u.FirstName + " " + u.LastName,
//FirstName = u.FirstName,
//LastName = u.LastName,
//Email = u.Email,
//UserName = EF.Functions.Like(u.UserName, "@%") ? "@" : u.UserName,
//IsLocked = u.LockoutEnd.HasValue,
//LockoutEnd = u.LockoutEnd,
//LastLogin = u.LastLogin
},
globalFilter => f => f.Name.Contains(globalFilter) || f.Description.Contains(globalFilter),
f => new
{
Type = f._Type,
Id = f.Id,
//Meta = new
//{
// Created = f.Created
//},
Attributes = new
{
// Email = f.Email,
// LastName = f.LastName,
// LockoutEnd = f.LockoutEnd,
// MemberId = f.MemberId,
Name = f.Name,
Description = f.Description
// UserName = f.UserName,
// LastLogin = f.LastLogin,
// IsLocked = f.IsLocked
}
},
null //account => account.Attributes.UserName = account.Attributes.UserName.StartsWith('@') ? "@" : account.Attributes.UserName
)));
}
public async Task <ActionResult> ListUsers(Dictionary <string, int> page, Dictionary <string, string> filter, string sort)
{
ListQueryStrategy mods = new ListQueryBuilder("LastName").Build(page, filter, sort, Request.Query);
return(Json(await mods.Run(
from u in appDb.Users
select new
{
_Type = "accounts",
Id = u.Id,
Created = u.Created,
MemberId = u.MemberId,
Name = u.FirstName + " " + u.LastName,
FirstName = u.FirstName,
LastName = u.LastName,
Email = u.Email,
UserName = EF.Functions.Like(u.UserName, "@%") ? "@" : u.UserName,
IsLocked = u.LockoutEnd.HasValue,
LockoutEnd = u.LockoutEnd,
LastLogin = u.LastLogin
},
globalFilter => f => f.Name.Contains(globalFilter) || f.Email.Contains(globalFilter) || f.UserName.Contains(globalFilter),
f => new
{
Type = f._Type,
Id = f.Id,
Meta = new
{
Created = f.Created
},
Attributes = new AccountModel
{
Email = f.Email,
LastName = f.LastName,
LockoutEnd = f.LockoutEnd,
MemberId = f.MemberId,
Name = f.Name,
UserName = f.UserName,
LastLogin = f.LastLogin,
IsLocked = f.IsLocked
}
},
account => account.Attributes.UserName = account.Attributes.UserName.StartsWith('@') ? "@" : account.Attributes.UserName
)));
}
public void Test_ListQuery_BuildThrowsExceptionWhenNoConfig()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("Person", new Mock.MockConnection());
var request = new ListQueryDefinition();
request.Filters = new List <ListFilter>(new ListFilter[] {
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
}
});
Assert.ThrowsException <System.Exception>(() =>
{
var stmt = ListQueryBuilder.Create(query, request)
.Apply();
});
}
public async Task <ActionResult> ListUserLogins(string userId, Dictionary <string, int> page, Dictionary <string, string> filter, string sort)
{
var oidcConfigs = JsonConvert.DeserializeObject <OidcConfig[]>(string.IsNullOrWhiteSpace(configuration["auth:external:oidc"]) ? "[]" : configuration["auth:external:oidc"]).ToDictionary(f => f.Id, f => f);
ListQueryStrategy mods = new ListQueryBuilder("ProviderDisplayName").Build(page, filter, sort, Request.Query);
return(Json(await mods.Run(
from l in appDb.UserLogins
where l.UserId == userId
select l,
globalFilter => f => f.ProviderDisplayName.Contains(globalFilter),
f => new
{
Type = "externallogins",
Id = f.LoginProvider + ":" + f.ProviderKey,
Meta = new ExternalLoginMeta
{
Icon = f.LoginProvider + ":" + f.ProviderKey,
Color = "b"
},
Attributes = new
{
DisplayName = f.ProviderDisplayName,
Provider = f.LoginProvider
},
Relationships = new
{
Owner = new
{
Data = new { Type = "accounts", Id = f.UserId }
}
}
},
row =>
{
row.Meta.Icon = row.Attributes.Provider == "Facebook" ? "fa-facebook-square" : row.Attributes.Provider == "Google" ? "fa-google" : oidcConfigs[row.Attributes.Provider].Icon;
row.Meta.Color = row.Attributes.Provider == "Facebook" ? "#3B5998" : row.Attributes.Provider == "Google" ? "#EA4335" : oidcConfigs[row.Attributes.Provider].IconColor;
}
)));
}
public void Test_ListQuery_ApplyEventHandlers()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
bool beforeInvoked = false;
bool afterInvoked = false;
queryDef.Order = new List <SortField>()
{
new SortField()
{
Name = "FirstName"
}
};
var qb = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.BeforeApply((config) =>
{
beforeInvoked = true;
Assert.IsNotNull(config);
Assert.AreEqual(0, config.QueryBuilder.OrderBy.Count);
config.QueryBuilder.OrderBy.Add("LastName");
})
.AfterApply((config, appliedQuery) =>
{
afterInvoked = true;
Assert.IsNotNull(config);
Assert.IsNotNull(appliedQuery);
Assert.AreEqual(2, appliedQuery.OrderBy.Count);
Assert.IsTrue(appliedQuery.OrderBy.Any(x => x.ColumnName == "LastName"));
})
.Apply();
Assert.IsTrue(beforeInvoked);
Assert.IsTrue(afterInvoked);
Assert.AreEqual(2, qb.OrderBy.Count);
}
public void Test_ListQuery_BuildWithNoValidation()
{
var expectedSql = "SELECT [t0].[FirstName],[t0].[LastName] FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p0);";
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Fields = new string[] { "FirstName", "LastName" };
queryDef.Filters = new List <ListFilter>(new ListFilter[] {
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
}
});
var stmt = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.UseStreamingResult()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
Assert.AreEqual(1, stmt.Parameters.Count);
}
public void Test_ListQuery_TypeDerivedHandler()
{
var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[PersonID] IN (SELECT [t1].[PersonID] FROM [dbo].[PersonPhoneNumber] AS [t1] WHERE ([t1].[PhoneNumber] LIKE @p0) AND ([t1].[PersonID]=[t0].[PersonID])));";
// SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[PersonID] IN (SELECT [t1].[PersonID] FROM [dbo].[PersonPhoneNumber] AS [t1] WHERE ([t1].[PhoneNumber] LIKE @p0) AND ([t1].[PersonID]=[t0].[PersonID])));
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Filters = new List <ListFilter>(new ListFilter[] {
new ListFilter()
{
Name = "PhoneNumber", Operator = ListFilterOperator.Like, Value = "555"
}
});
var stmt = ListQueryBuilder.Create(query, queryDef)
.ValidateWith <Mock.Person>()
.AddFilterHandlers <Mock.PersonFilters>()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
Assert.AreEqual(1, stmt.Parameters.Count);
}
