private void AddAdditionalCriteria(IQuery query)
{
foreach (var criteria in AdditionalCriteria)
{
criteria(query);
}
}
#pragma warning disable 1591 // Xml Comments
public QueryResult Execute(IQuery query, PagingInfo paging)
{
ThrowIfNoQueryPropertyOnQuery(query);
var result = QueryResult.For(query);
var authorizationResult = _fetchingSecurityManager.Authorize(query);
if (!authorizationResult.IsAuthorized)
{
result.SecurityMessages = authorizationResult.BuildFailedAuthorizationMessages();
return result;
}
try
{
var property = GetQueryPropertyFromQuery(query);
var queryProviderType = GetActualProviderTypeFrom(property.PropertyType);
ThrowIfUnknownQueryType(queryProviderType, query, property);
var provider = _container.Get(queryProviderType);
var actualQuery = property.GetValue(query, null);
var providerResult = ExecuteOnProvider(provider, actualQuery, paging);
result.TotalItems = providerResult.TotalItems;
var readModels = providerResult.Items as IEnumerable<IReadModel>;
result.Items = _filters.Filter(readModels);
}
catch (TargetInvocationException ex)
{
result.Exception = ex.InnerException;
}
catch (Exception ex)
{
result.Exception = ex;
}
return result;
}
private static void CreateSystemRoles(IQuery query)
{
query.Access().CreateRole(SystemRoles.SecureAccessRole);
query.Access().CreateRole(SystemRoles.UserManagerRole);
query.Access().CreateRole(SystemRoles.SchemaManagerRole);
query.Access().CreateRole(SystemRoles.LockedRole);
}
public UniqueAttribute(Type queryType, string nameOfMethodThatReturnsIList)
{
if (typeof(IQuery).IsAssignableFrom(queryType))
{
throw new InvalidTypeException(string.Format("The specified type '{0}' does not implement IQuery", queryType.Name));
}
_query = DependencyResolver.Current.GetService(queryType) as IQuery;
if (_query == null)
{
throw new TypeNotRegisteredException(
string.Format("The type '{0}' is not registered, and cannot be used to validate the uniqueness of a property", queryType.Name));
}
_queryMethod = queryType.GetMethod(nameOfMethodThatReturnsIList);
if (_queryMethod == null)
{
throw new InvalidMethodException(string.Format("The method '{0}.{1}' could not be found", queryType, nameOfMethodThatReturnsIList));
}
if (!typeof(IList).IsAssignableFrom(_queryMethod.ReturnType))
{
throw new InvalidMethodException(
string.Format(
"Really?! The method '{0}' assigned to nameOfMethodThatReturnsIList does not return an IEnumerable",
nameOfMethodThatReturnsIList));
}
}
/// <summary>
/// <![CDATA[
/// 将聚合子表达式解析为嵌入式子查询条件。
/// 例如：
/// 将表达式
/// book.ChapterList.Cast<Chapter>().Any(c => c.Name == chapterName)
/// 转换为：
/// f.Exists(f.Query(chapter,
/// where: f.And(
/// f.Constraint(chapter.Column(Chapter.BookIdProperty), book.IdColumn),
/// f.Constraint(chapter.Column(Chapter.NameProperty), chapterName)
/// )
/// ))
/// SQL：
/// SELECT * FROM [Book] b
/// WHERE EXISTS(
/// SELECT * FROM [Chapter] c
/// WHERE c.BookId = b.Id AND
/// c.Name = {0}
/// )
/// ]]>
/// </summary>
/// <param name="exp">需要解析的表达式</param>
/// <param name="parentQuery"></param>
/// <param name="propertyFinder">The property finder.</param>
internal IConstraint Build(Expression exp, IQuery parentQuery, PropertyFinder propertyFinder)
{
_parentQuery = parentQuery;
_parentPropertyFinder = propertyFinder;
this.Visit(exp);
var res = f.Exists(_query);
if (!_isAny) res = f.Not(res);
//如果父查询中需要反转条件，则返回 NOT 语句。
if (propertyFinder.ReverseConstraint)
{
res = f.Not(res);
}
//把可空外键的不可空条件，与 Exists 条件合并后返回。
if (propertyFinder.NullableRefConstraint != null)
{
var op = propertyFinder.ReverseConstraint ? BinaryOperator.Or : BinaryOperator.And;
res = f.Binary(propertyFinder.NullableRefConstraint, op, res);
}
return res;
}
public virtual ISearchResults Search(IQuery query)
{
using (var context = ContentSearchManager.GetIndex((SitecoreIndexableItem)Context.Item).CreateSearchContext())
{
var root = this.Settings.Root;
var queryable = context.GetQueryable<SearchResultItem>();
queryable = SetQueryRoot(queryable, root);
queryable = this.FilterOnPresentationOnly(queryable);
queryable = FilterOnLanguage(queryable);
queryable = FilterOnVersion(queryable);
if (this.Settings.Templates != null && this.Settings.Templates.Any())
{
queryable = queryable.Cast<IndexedItem>().Where(this.GetTemplatePredicates(this.Settings.Templates));
}
else
{
queryable = this.FilterOnTemplates(queryable);
}
queryable = this.AddContentPredicates(queryable, query);
queryable = AddFacets(queryable);
if (query.IndexOfFirstResult > 0)
{
queryable = queryable.Skip(query.IndexOfFirstResult);
}
if (query.NoOfResults > 0)
{
queryable = queryable.Take(query.NoOfResults);
}
var results = queryable.GetResults();
return SearchResultsRepository.Create(results, query);
}
}
public void ExecuteQuery(IQuery<IEnumerable<Article>> query)
{
var predicateQuery = query as IPredicateQuery<Article>;
var result = this.articles.Where(a => predicateQuery.Predicate(a));
query.Callback(result);
}
public List<Group> GetGroups(IQuery<Group> query = null)
{
using (var repositoriesContainer = new LmPlatformRepositoriesContainer())
{
return repositoriesContainer.GroupsRepository.GetAll(query).ToList();
}
}
public void Optimize(IQuery q, object predicate, MethodBase filterMethod)
{
// TODO: cache predicate expressions here
var builder = new QueryExpressionBuilder();
var expression = builder.FromMethod(filterMethod);
new SODAQueryBuilder().OptimizeQuery(expression, q, predicate, _classFactory, new CecilReferenceResolver());
}
public ElasticQuery(IQuery query, SortItem sortItem, int from, int size)
{
Query = query;
SortItems = new List<SortItem>() { sortItem };
From = from;
Size = size;
}
public virtual ISearchResults Search(IQuery query)
{
using (var context = ContentSearchManager.GetIndex(this.IndexName).CreateSearchContext())
{
var root = this.Settings.Root;
var queryable = context.GetQueryable<SearchResultItem>();
queryable = SetQueryRoot(queryable, root);
queryable = this.FilterOnPresentationOnly(queryable);
queryable = FilterOnLanguage(queryable);
queryable = this.FilterOnTemplates(queryable);
queryable = this.AddContentPredicates(queryable, query);
queryable = AddFacets(queryable);
if (query.IndexOfFirstResult > 0)
{
queryable = queryable.Skip(query.IndexOfFirstResult);
}
if (query.NoOfResults > 0)
{
queryable = queryable.Take(query.NoOfResults);
}
var results = queryable.GetResults();
return SearchResultsRepository.Create(results, query);
}
}
public virtual string Query(IQuery query)
{
try
{
StringBuilder str = new StringBuilder();
IReadOnlyList<SingleEntityView> svList = _vv.VisitAlias(query.View);
str.Append(_tr.Select);
str.Append(_cb.BuildSelectColumns(query.View));
str.Append(_tr.From);
str.Append(_vv.BuildJoinClause(query.View));
if (query.Criteria != null)
{
str.Append(_tr.Where);
str.Append(_cv.BuildWhereFilters(query.Criteria, svList));
}
if (query.Order != null)
{
str.Append(_tr.OrderBy);
str.Append(_ov.BuildOrderClause(query.Order, svList));
}
return str.ToString();
}
catch (Exception ex)
{
throw ex.CreateWrapException<SQLGenerateException>();
}
}
public IObservable<string> GetStream(IQuery query)
{
if (query == null) throw new ArgumentNullException(nameof(query));
var requestProvider = GetRequestProvider(query);
var observable = Observable.Create<string>(async observer =>
{
//If exception thrown we retry ten times
//TODO: Config
for (var i = 0; i < 10; i++)
{
try
{
await _listener.Listen(requestProvider, observer.OnNext);
}
catch (Exception exception)
{
observer.OnError(exception);
continue;
}
break;
}
observer.OnCompleted();
});
return observable;
}
public _IRunnable_50(EventRegistryImpl _enclosing, Transaction transaction, IQuery
query)
{
this._enclosing = _enclosing;
this.transaction = transaction;
this.query = query;
}
internal override bool MatchesInvoke(Invoke invoke, IQuery query)
{
if (invoke == null)
return false;
bool ignoreCase = true;
if (query != null)
ignoreCase = query.IgnoreIdentifiersCase();
if (!RoutineName.Equals(invoke.RoutineName, ignoreCase))
return false;
var inputParams = Parameters.Where(parameter => parameter.IsInput).ToList();
if (invoke.Arguments.Length != inputParams.Count)
return false;
for (int i = 0; i < invoke.Arguments.Length; i++) {
// TODO: support variable evaluation here? or evaluate parameters before reaching here?
if (!invoke.Arguments[i].IsConstant())
return false;
var argType = invoke.Arguments[i].ReturnType(query, null);
var paramType = Parameters[i].Type;
// TODO: verify if this is assignable (castable) ...
if (!paramType.IsComparable(argType))
return false;
}
return true;
}
protected override bool OnSetUp(string testName, IQuery query)
{
CreateTestUser(query);
CreateTestRole(query);
CreateTestTable(query);
return true;
}
private void InsertData(IQuery query)
{
var tableName = ObjectName.Parse("APP.persons");
var table = query.Access().GetMutableTable(tableName);
var row = table.NewRow();
row["name"] = Field.String("Antonello Provenzano");
row["age"] = Field.Integer(36);
row.SetDefault(query);
table.AddRow(row);
row = table.NewRow();
row["name"] = Field.String("Sebastiano Provenzano");
row["age"] = Field.Integer(35);
row.SetDefault(query);
table.AddRow(row);
row = table.NewRow();
row["name"] = Field.String("Mart Rosmaa");
row["age"] = Field.Integer(33);
row.SetDefault(query);
table.AddRow(row);
row = table.NewRow();
row["name"] = Field.String("Karl Inge Stensson");
row["age"] = Field.Integer(54);
row.SetDefault(query);
table.AddRow(row);
}
public void GetQueryPredicate_NewsItemWithWrongContent_ShouldReturnFalse(string fieldName, NewsIndexingProvider provider, IQuery query, string queryText, string contentText)
{
var item = Substitute.For<SearchResultItem>();
query.QueryText.Returns(queryText);
item[fieldName].Returns(contentText);
provider.GetQueryPredicate(query).Compile().Invoke(item).Should().BeFalse();
}
/// <summary>
/// 資料庫連線名稱ByString
/// </summary>
/// <param name="Connection"></param>
public ODAL(string Connection)
{
objCon = new ConnectionString(Connection);
cmd = GetDbCommand;
if (objCon.ProviderName == "System.Data.SqlClient")
{
Provider = new MSSQL(objCon);
}
else if (objCon.ProviderName == "System.Data.OleDb")
{
Provider = new OleDb(objCon);
}
else if (objCon.ProviderName == "System.Data.OracleClient")
{
Provider = new Oracle(objCon);
}
else if (objCon.ProviderName == "MySql.Data.MySqlClient")
{
Provider = new Mysql(objCon);
}
else
{
throw new Exception("需指定資料庫類型！");
}
}
/// <summary>
/// Gets a paginated list of APObjects matching the given search criteria.
/// </summary>
/// <param name="type">The object type.</param>
/// <param name="query">The search query for objects to be found.</param>
/// <param name="fields">The object fields to be returned for the matching list of objects.</param>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">The page size.</param>
/// <param name="orderBy">The object field on which the results should be sorted.</param>
/// <param name="sortOrder">The sort order.</param>
/// <param name="options">Request specific api options. These will override the global settings for the app for this request.</param>
/// <returns>Paginated list of APObject objects matching the given search criteria.</returns>
public async static Task<PagedList<APObject>> FindAllAsync(string type, IQuery query = null, IEnumerable<string> fields = null, int pageNumber = 1, int pageSize = 20, string orderBy = null, SortOrder sortOrder = SortOrder.Descending, ApiOptions options = null)
{
query = query ?? Query.None;
var request = new FindAllObjectsRequest()
{
Type = type,
Query = query.AsString().Escape(),
PageNumber = pageNumber,
PageSize = pageSize,
OrderBy = orderBy,
SortOrder = sortOrder
};
if( fields != null )
request.Fields.AddRange(fields);
ApiOptions.Apply(request, options);
var response = await request.ExecuteAsync();
if (response.Status.IsSuccessful == false)
throw response.Status.ToFault();
var objects = new PagedList<APObject>()
{
PageNumber = response.PagingInfo.PageNumber,
PageSize = response.PagingInfo.PageSize,
TotalRecords = response.PagingInfo.TotalRecords,
GetNextPage = async skip => await FindAllAsync(type, query, fields, pageNumber + skip + 1, pageSize, orderBy, sortOrder, options)
};
objects.AddRange(response.Objects);
return objects;
}
public InterfaceSelectionItemsSource(
ClassifierDictionary classifiers,
IQuery<Classifier> availableClassifiers,
MessageSystem messageSystem):base(classifiers,availableClassifiers.Get,messageSystem)
{
_classifiers = classifiers;
}
private void AddTestData(IQuery context)
{
var table = context.GetMutableTable(ObjectName.Parse("APP.people"));
var row = table.NewRow();
// row.SetValue("id", DataObject.Integer(0));
row.SetDefault(0, context);
row.SetValue("first_name", DataObject.String("John"));
row.SetValue("last_name", DataObject.String("Doe"));
row.SetValue("birth_date", DataObject.Date(new SqlDateTime(1977, 01, 01)));
row.SetValue("active", DataObject.Boolean(false));
table.AddRow(row);
row = table.NewRow();
// row.SetValue("id", DataObject.Integer(1));
row.SetDefault(0, context);
row.SetValue("first_name", DataObject.String("Jane"));
row.SetValue("last_name", DataObject.String("Doe"));
row.SetValue("birth_date", DataObject.Date(new SqlDateTime(1978, 11, 01)));
row.SetValue("active", DataObject.Boolean(true));
table.AddRow(row);
row = table.NewRow();
// row.SetValue("id", DataObject.Integer(2));
row.SetDefault(0, context);
row.SetValue("first_name", DataObject.String("Roger"));
row.SetValue("last_name", DataObject.String("Rabbit"));
row.SetValue("birth_date", DataObject.Date(new SqlDateTime(1985, 05, 05)));
row.SetValue("active", DataObject.Boolean(true));
table.AddRow(row);
context.Commit();
}
public async Task FilterAsync(IQuery<ContentItem> query, ListContentsViewModel model, PagerParameters pagerParameters, IUpdateModel updateModel)
{
var viewModel = new ListPartContentAdminFilterModel();
if(await updateModel.TryUpdateModelAsync(viewModel, ""))
{
// Show list content items
if (viewModel.ShowListContentTypes)
{
var listableTypes = _contentDefinitionManager
.ListTypeDefinitions()
.Where(x =>
x.Parts.Any(p =>
p.PartDefinition.Name == nameof(ListPart)))
.Select(x => x.Name);
query.With<ContentItemIndex>(x => x.ContentType.IsIn(listableTypes));
}
// Show contained elements for the specified list
else if(viewModel.ListContentItemId != 0)
{
query.With<ContainedPartIndex>(x => x.ListContentItemId == viewModel.ListContentItemId);
}
}
}
private void CreateTable(IQuery query)
{
var tableInfo = new TableInfo(ObjectName.Parse("APP.test_table"));
tableInfo.AddColumn("a", PrimitiveTypes.Integer());
tableInfo.AddColumn("b", PrimitiveTypes.String(), false);
query.CreateTable(tableInfo, false, false);
}
public NotFilter(IQuery query)
{
if (query == null)
throw new ArgumentNullException("query", "NotFilter requires a query for this constructor.");
Query = query;
}
private static void Create(IQuery systemQuery)
{
// SYSTEM.ROUTINE
systemQuery.Access()
.CreateTable(table => table
.Named(RoutineManager.RoutineTableName)
.WithColumn("id", PrimitiveTypes.Numeric())
.WithColumn("schema", PrimitiveTypes.String())
.WithColumn("name", PrimitiveTypes.String())
.WithColumn("type", PrimitiveTypes.String())
.WithColumn("location", PrimitiveTypes.String())
.WithColumn("body", PrimitiveTypes.Binary())
.WithColumn("return_type", PrimitiveTypes.String())
.WithColumn("username", PrimitiveTypes.String()));
// SYSTEM.ROUTINE_PARAM
systemQuery.Access().CreateTable(table => table
.Named(RoutineManager.RoutineParameterTableName)
.WithColumn("routine_id", PrimitiveTypes.Numeric())
.WithColumn("arg_name", PrimitiveTypes.String())
.WithColumn("arg_type", PrimitiveTypes.String())
.WithColumn("arg_attrs", PrimitiveTypes.Numeric())
.WithColumn("in_out", PrimitiveTypes.Integer())
.WithColumn("offset", PrimitiveTypes.Integer()));
}
protected override bool OnSetUp(string testName, IQuery query)
{
var seqName = ObjectName.Parse("APP.seq1");
query.Access().CreateObject(new SequenceInfo(seqName, new SqlNumber(0), new SqlNumber(1), new SqlNumber(0),
new SqlNumber(Int16.MaxValue), 256));
return true;
}
protected Expression<Func<SearchResultItem, bool>> GetFreeTextPredicate(string[] fieldNames, IQuery query)
{
var predicate = PredicateBuilder.False<SearchResultItem>();
if (string.IsNullOrWhiteSpace(query.QueryText))
return predicate;
return fieldNames.Aggregate(predicate, (current, fieldName) => current.Or(i => i[fieldName].Contains(query.QueryText)));
}
private void SetParameters(IQuery query)
{
foreach (var parameter in NamedParameters)
{
query.SetParameter(parameter.Name, parameter.Value);
}
}
/// <summary>
/// Create a query filter.
/// </summary>
/// <param name="query">Set the query this filter uses to search documents.</param>
public QueryFilter(IQuery query)
{
if (query == null)
throw new ArgumentNullException("query", "QueryFilter requires a query.");
Query = query;
}
public static Query <T> CreateFromNonGeneric(IQuery query, Func <Query <T>, IEnumerable <T> >?dataProvider = null, Func <LambdaExpression, Expressions.LambdaExpression>?expressionTranslator = null, ITypeResolver?typeResolver = null) => query.ToGenericQuery <T>(dataProvider, expressionTranslator, typeResolver);
public static Task <TDto> FirstOrDefaultAsync <TEntity, TDto>(this IQueryable <TEntity> source, IQuery query)
where TEntity : class
{
var data = source.Where(query).ProjectTo <TDto>();
if (query is ISortInfo sort)
{
data = data.OrderBy(sort);
}
return(data.FirstOrDefaultAsync());
}
protected override bool OnSetUp(string testName, IQuery query)
{
CreateTable(query);
InsertTestData(query);
return(true);
}
/// <summary>
/// 查询指定条件的数据
/// </summary>
/// <typeparam name="TEntity">查询的实体</typeparam>
/// <typeparam name="TDto">返回的类型</typeparam>
/// <param name="source"></param>
/// <param name="query">查询条件</param>
/// <param name="page">分页信息</param>
/// <param name="defaultSort">默认排序</param>
public static async Task <PagingResult <TDto> > ToPagingResultAsync <TEntity, TDto>(this IQueryable <TEntity> source, IQuery query, IPagingSortInfo page, string defaultSort = null)
where TEntity : class
{
page = page ?? new PagingSortInfo();
var pageIndex = Math.Max(1, page.PageIndex);
var pageSize = Math.Max(1, page.PageSize);
var result = new PagingResult <TDto>()
{
PageIndex = pageIndex, PageSize = pageSize
};
var data = source.Where(query);
result.TotalCount = await data.CountAsync();
if (result.TotalCount > 0)
{
var mapData = data.OrderBy(page, defaultSort).ProjectTo <TDto>();
result.Data = await mapData.Skip(pageSize *(pageIndex - 1))
.Take(pageSize).ToListAsync();
}
return(result);
}
public static Task <List <TDto> > ToListAsync <TEntity, TDto>(this IQueryable <TEntity> source, IQuery query)
where TEntity : class
{
var data = source.Where(query).ProjectTo <TDto>();
return(data.ToListAsync());
}
public static Task <List <TDto> > ToListAsync <TEntity, TDto>(this IQueryable <TEntity> source, IQuery query, ISortInfo sort, string defaultSort = null)
where TEntity : class
{
var data = source.Where(query).ProjectTo <TDto>().OrderBy(sort, defaultSort);
return(data.ToListAsync());
}
public static void BasicQuery()
{
IQuery <User> q = context.Query <User>();
q.Where(a => a.Id == 1).FirstOrDefault();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE [Users].[Id] = 1 LIMIT 1 OFFSET 0
*/
//可以选取指定的字段
q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name] FROM [Users] AS [Users] WHERE [Users].[Id] = 1 LIMIT 1 OFFSET 0
*/
//分页
var result = q.Where(a => a.Id > 0).OrderBy(a => a.Age).Skip(20).Take(10).ToList();
/*
* SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE [Users].[Id] > 0 ORDER BY [Users].[Age] ASC LIMIT 10 OFFSET 20
*/
/* like 查询 */
q.Where(a => a.Name.Contains("so") || a.Name.StartsWith("s") || a.Name.EndsWith("o")).ToList();
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE ([Users].[Name] LIKE '%' || 'so' || '%' OR [Users].[Name] LIKE 's' || '%' OR [Users].[Name] LIKE '%' || 'o')
*/
/* in 一个数组 */
List <User> users = null;
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = q.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[Id] IN (1,2,3)
*/
/* in 子查询 */
users = q.Where(a => context.Query <City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[CityId] IN (SELECT [City].[Id] AS [C] FROM [City] AS [City])
*/
/* distinct 查询 */
q.Select(a => new { a.Name }).Distinct().ToList();
/*
* SELECT DISTINCT [Users].[Name] AS [Name] FROM [Users] AS [Users]
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static IQueryable <TEntity> Where <TEntity>(this IQueryable <TEntity> source, IQuery query)
where TEntity : class
{
var filter = query?.GetFilter <TEntity>();
if (filter != null)
{
source = source.Where(filter);
}
return(source);
}
public async Task QueryModifiableIterateAsync()
{
long lastDataPointId = 0;
int nExpectedChanges = 0;
using (ISession s = OpenSession())
{
s.CacheMode = CacheMode.Ignore;
using (ITransaction t = s.BeginTransaction())
{
DataPoint dp = null;
for (int i = 0; i < 100; i++)
{
dp = new DataPoint();
dp.X = 0.1M * i;
dp.Y = (decimal)System.Math.Cos((double)dp.X);
await(s.SaveAsync(dp));
}
await(t.CommitAsync());
lastDataPointId = dp.Id;
}
}
using (ISession s = OpenSession())
{
s.CacheMode = CacheMode.Ignore;
using (ITransaction t = s.BeginTransaction())
{
s.DefaultReadOnly = true;
IQuery query = s.CreateQuery("from DataPoint dp order by dp.X asc");
Assert.That(query.IsReadOnly, Is.True);
s.DefaultReadOnly = false;
Assert.That(query.IsReadOnly, Is.False);
s.DefaultReadOnly = true;
Assert.That(query.IsReadOnly, Is.True);
query.SetReadOnly(false);
Assert.That(query.IsReadOnly, Is.False);
s.DefaultReadOnly = false;
Assert.That(query.IsReadOnly, Is.False);
s.DefaultReadOnly = true;
Assert.That(query.IsReadOnly, Is.False);
query.SetReadOnly(true);
Assert.That(query.IsReadOnly, Is.True);
s.DefaultReadOnly = false;
Assert.That(query.IsReadOnly, Is.True);
query.SetReadOnly(false);
Assert.That(query.IsReadOnly, Is.False);
s.DefaultReadOnly = true;
Assert.That(s.DefaultReadOnly, Is.True);
IEnumerator <DataPoint> it = (await(query.EnumerableAsync <DataPoint>())).GetEnumerator();
Assert.That(query.IsReadOnly, Is.False);
DataPoint dpLast = await(s.GetAsync <DataPoint>(lastDataPointId));
Assert.That(s.IsReadOnly(dpLast), Is.True);
query.SetReadOnly(true);
Assert.That(query.IsReadOnly, Is.True);
Assert.That(s.DefaultReadOnly, Is.True);
int i = 0;
while (it.MoveNext())
{
Assert.That(s.DefaultReadOnly, Is.True);
DataPoint dp = it.Current;
Assert.That(s.DefaultReadOnly, Is.True);
if (dp.Id == dpLast.Id)
{
//dpLast existed in the session before executing the read-only query
Assert.That(s.IsReadOnly(dp), Is.True);
}
else
{
Assert.That(s.IsReadOnly(dp), Is.False);
}
if (++i == 50)
{
s.SetReadOnly(dp, true);
nExpectedChanges = (dp == dpLast ? 99 : 98);
}
dp.Description = "done!";
}
Assert.That(s.DefaultReadOnly, Is.True);
await(t.CommitAsync());
}
s.Clear();
using (ITransaction t = s.BeginTransaction())
{
try
{
IList list = await(s.CreateQuery("from DataPoint where Description = 'done!'").ListAsync());
Assert.That(list.Count, Is.EqualTo(nExpectedChanges));
}
finally
{
// cleanup
await(s.CreateQuery("delete from DataPoint").ExecuteUpdateAsync());
}
await(t.CommitAsync());
}
}
}
public static void Method()
{
IQuery <User> q = context.Query <User>();
var space = new char[] { ' ' };
DateTime startTime = DateTime.Now;
DateTime endTime = DateTime.Now.AddDays(1);
var ret = q.Select(a => new
{
Id = a.Id,
String_Length = (int?)a.Name.Length, //LENGTH([Users].[Name])
Substring = a.Name.Substring(0), //SUBSTR([Users].[Name],0 + 1)
Substring1 = a.Name.Substring(1), //SUBSTR([Users].[Name],1 + 1)
Substring1_2 = a.Name.Substring(1, 2), //SUBSTR([Users].[Name],1 + 1,2)
ToLower = a.Name.ToLower(), //LOWER([Users].[Name])
ToUpper = a.Name.ToUpper(), //UPPER([Users].[Name])
IsNullOrEmpty = string.IsNullOrEmpty(a.Name), //CASE WHEN ([Users].[Name] IS NULL OR [Users].[Name] = '') THEN 1 ELSE 0 END = 1
Contains = (bool?)a.Name.Contains("s"), //[Users].[Name] LIKE '%' || 's' || '%'
StartsWith = (bool?)a.Name.StartsWith("s"), //[Users].[Name] LIKE 's' || '%'
EndsWith = (bool?)a.Name.EndsWith("s"), //[Users].[Name] LIKE '%' || 's'
Trim = a.Name.Trim(), //TRIM([Users].[Name])
TrimStart = a.Name.TrimStart(space), //LTRIM([Users].[Name])
TrimEnd = a.Name.TrimEnd(space), //RTRIM([Users].[Name])
Replace = a.Name.Replace("l", "L"),
DiffYears = Sql.DiffYears(startTime, endTime), //(CAST(STRFTIME('%Y',@P_0) AS INTEGER) - CAST(STRFTIME('%Y',@P_1) AS INTEGER))
DiffMonths = Sql.DiffMonths(startTime, endTime), //((CAST(STRFTIME('%Y',@P_0) AS INTEGER) - CAST(STRFTIME('%Y',@P_1) AS INTEGER)) * 12 + (CAST(STRFTIME('%m',@P_0) AS INTEGER) - CAST(STRFTIME('%m',@P_1) AS INTEGER)))
DiffDays = Sql.DiffDays(startTime, endTime), //CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) AS INTEGER)
DiffHours = Sql.DiffHours(startTime, endTime), //CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 24 AS INTEGER)
DiffMinutes = Sql.DiffMinutes(startTime, endTime), //CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 1440 AS INTEGER)
DiffSeconds = Sql.DiffSeconds(startTime, endTime), //CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 86400 AS INTEGER)
//DiffMilliseconds = Sql.DiffMilliseconds(startTime, endTime),//不支持 Millisecond
//DiffMicroseconds = Sql.DiffMicroseconds(startTime, endTime),//不支持 Microseconds
AddYears = startTime.AddYears(1), //DATETIME(@P_0,'+' || 1 || ' years')
AddMonths = startTime.AddMonths(1), //DATETIME(@P_0,'+' || 1 || ' months')
AddDays = startTime.AddDays(1), //DATETIME(@P_0,'+' || 1 || ' days')
AddHours = startTime.AddHours(1), //DATETIME(@P_0,'+' || 1 || ' hours')
AddMinutes = startTime.AddMinutes(2), //DATETIME(@P_0,'+' || 2 || ' minutes')
AddSeconds = startTime.AddSeconds(120), //DATETIME(@P_0,'+' || 120 || ' seconds')
//AddMilliseconds = startTime.AddMilliseconds(2000),//不支持
Now = DateTime.Now, //DATETIME('NOW','LOCALTIME')
UtcNow = DateTime.UtcNow, //DATETIME()
Today = DateTime.Today, //DATE('NOW','LOCALTIME')
Date = DateTime.Now.Date, //DATE('NOW','LOCALTIME')
Year = DateTime.Now.Year, //CAST(STRFTIME('%Y',DATETIME('NOW','LOCALTIME')) AS INTEGER)
Month = DateTime.Now.Month, //CAST(STRFTIME('%m',DATETIME('NOW','LOCALTIME')) AS INTEGER)
Day = DateTime.Now.Day, //CAST(STRFTIME('%d',DATETIME('NOW','LOCALTIME')) AS INTEGER)
Hour = DateTime.Now.Hour, //CAST(STRFTIME('%H',DATETIME('NOW','LOCALTIME')) AS INTEGER)
Minute = DateTime.Now.Minute, //CAST(STRFTIME('%M',DATETIME('NOW','LOCALTIME')) AS INTEGER)
Second = DateTime.Now.Second, //CAST(STRFTIME('%S',DATETIME('NOW','LOCALTIME')) AS INTEGER)
Millisecond = DateTime.Now.Millisecond, //@P_2 直接计算 DateTime.Now.Millisecond 的值
DayOfWeek = DateTime.Now.DayOfWeek, //CAST(STRFTIME('%w',DATETIME('NOW','LOCALTIME')) AS INTEGER)
Byte_Parse = byte.Parse("1"), //CAST('1' AS INTEGER)
Int_Parse = int.Parse("1"), //CAST('1' AS INTEGER)
Int16_Parse = Int16.Parse("11"), //CAST('11' AS INTEGER)
Long_Parse = long.Parse("2"), //CAST('2' AS INTEGER)
Double_Parse = double.Parse("3.1"), //CAST('3.1' AS REAL)
Float_Parse = float.Parse("4.1"), //CAST('4.1' AS REAL)
//Decimal_Parse = decimal.Parse("5"),//不支持
//Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//不支持 'D544BC4C-739E-4CD3-A3D3-7BF803FCE179'
Bool_Parse = bool.Parse("1"), //CAST('1' AS INTEGER)
DateTime_Parse = DateTime.Parse("2014-01-01"), //DATETIME('2014-01-01')
B = a.Age == null ? false : a.Age > 1, //三元表达式
CaseWhen = Case.When(a.Id > 100).Then(1).Else(0) //case when
}).ToList();
ConsoleHelper.WriteLineAndReadKey();
}
public EmployeeRepo(IConfiguration config, IQuery cmdText)
{
_cmdText = cmdText;
_connStr = config.GetConnectionString("TricorConStr");
}
/*复杂查询*/
public static void ComplexQuery()
{
/*
* 支持 select * from Users where CityId in (1,2,3) --in一个数组
* 支持 select * from Users where CityId in (select Id from City) --in子查询
* 支持 select * from Users exists (select 1 from City where City.Id=Users.CityId) --exists查询
* 支持 select (select top 1 CityName from City where Users.CityId==City.Id) as CityName, Users.Id, Users.Name from Users --select子查询
* 支持 select
* (select count(*) from Users where Users.CityId=City.Id) as UserCount, --总数
* (select max(Users.Age) from Users where Users.CityId=City.Id) as MaxAge, --最大年龄
* (select avg(Users.Age) from Users where Users.CityId=City.Id) as AvgAge --平均年龄
* from City
* --统计查询
*/
IQuery <User> userQuery = context.Query <User>();
IQuery <City> cityQuery = context.Query <City>();
List <User> users = null;
/* in 一个数组 */
List <int> userIds = new List <int>()
{
1, 2, 3
};
users = userQuery.Where(a => userIds.Contains(a.Id)).ToList(); /* list.Contains() 方法组合就会生成 in一个数组 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[Id] IN (1,2,3)
*/
/* in 子查询 */
users = userQuery.Where(a => cityQuery.Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); /* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE [Users].[CityId] IN (SELECT [City].[Id] AS [C] FROM [City] AS [City])
*/
/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
users = userQuery.Where(a => cityQuery.Where(c => c.Id == a.CityId).Any()).ToList();
/*
* SELECT
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
* WHERE Exists (SELECT '1' AS [C] FROM [City] AS [City] WHERE [City].[Id] = [Users].[CityId])
*/
/* select 子查询 */
var result = userQuery.Select(a => new
{
CityName = cityQuery.Where(c => c.Id == a.CityId).First().Name,
User = a
}).ToList();
/*
* SELECT
* (SELECT [City].[Name] AS [C] FROM [City] AS [City] WHERE [City].[Id] = [Users].[CityId] LIMIT 1 OFFSET 0) AS [CityName],
* [Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[Users].[Id] AS [Id],[Users].[Name] AS [Name]
* FROM [Users] AS [Users]
*/
/* 统计 */
var statisticsResult = cityQuery.Select(a => new
{
UserCount = userQuery.Where(u => u.CityId == a.Id).Count(),
MaxAge = userQuery.Where(u => u.CityId == a.Id).Max(c => c.Age),
AvgAge = userQuery.Where(u => u.CityId == a.Id).Average(c => c.Age),
}).ToList();
/*
* SELECT
* (SELECT COUNT(1) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [UserCount],
* (SELECT MAX([Users].[Age]) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [MaxAge],
* (SELECT CAST(AVG([Users].[Age]) AS REAL) AS [C] FROM [Users] AS [Users] WHERE [Users].[CityId] = [City].[Id]) AS [AvgAge]
* FROM [City] AS [City]
*/
ConsoleHelper.WriteLineAndReadKey();
}
public DictionaryFormScript(IQuery query, string displayName) : base(displayName)
{
_query = query;
}
public async Task ModifiableSessionDefaultQueryReadOnlySessionIterateAsync()
{
using (ISession s = OpenSession())
{
s.CacheMode = CacheMode.Ignore;
using (ITransaction t = s.BeginTransaction())
{
for (int i = 0; i < 100; i++)
{
DataPoint dp = new DataPoint();
dp.X = 0.1M * i;
dp.Y = (decimal)System.Math.Cos((double)dp.X);
await(s.SaveAsync(dp));
}
await(t.CommitAsync());
}
}
using (ISession s = OpenSession())
{
s.CacheMode = CacheMode.Ignore;
using (ITransaction t = s.BeginTransaction())
{
s.DefaultReadOnly = false;
IQuery query = s.CreateQuery("from DataPoint dp order by dp.X asc");
s.DefaultReadOnly = true;
IEnumerable enumerable = await(query.EnumerableAsync());
s.DefaultReadOnly = false;
int i = 0;
foreach (DataPoint dp in enumerable)
{
if (++i == 50)
{
s.SetReadOnly(dp, false);
}
dp.Description = "done!";
}
await(t.CommitAsync());
}
s.Clear();
using (ITransaction t = s.BeginTransaction())
{
try
{
IList single = await(s.CreateQuery("from DataPoint where Description = 'done!'").ListAsync());
Assert.That(single.Count, Is.EqualTo(1));
}
finally
{
// cleanup
await(s.CreateQuery("delete from DataPoint").ExecuteUpdateAsync());
}
await(t.CommitAsync());
}
}
}
public ExistsPredicate(IQuery query)
{
this.Comments = new Comments(3);
this.Query = query;
}
/// <summary>
/// Was generated the same SQL.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="value">The value.</param>
/// <param name="expectedSql">The expected SQL.</param>
/// <param name="parameters">The parameters.</param>
protected virtual void WasGeneratedSameSql <T>(IQuery <T> value, string expectedSql, params object[] parameters)
{
var provider = value.Provider as FakeQueryProvider;
AreSame(provider.LastExpression.Expression, new QueryInfo(expectedSql), parameters);
}
public Expression <Func <SearchResultItem, bool> > GetQueryPredicate(IQuery query)
{
var fieldNames = new[] { Templates.BaseField.Fields.stringTitle };
return(GetFreeTextPredicateService.GetFreeTextPredicate(fieldNames, query));
}
public async Task <TResult> ExecuteAsync <TResult>(IQuery <TResult> query, IExecutionContext executionContext)
{
return(await _innerDomainRepositoryExecutor.ExecuteAsync(query, _executionContextOverride));
}
public FirmAddressMustBeLocatedOnTheMap(IQuery query) : base(query, MessageTypeCode.FirmAddressMustBeLocatedOnTheMap)
{
}
internal ExistsPredicate(IQuery query, Comments comments)
{
this.Comments = comments;
this.Query = query;
}
public static List <dynamic> GetPageRecords(IQuery queryParams)
{
return(GetPageRecords <dynamic>(queryParams));
}
protected override IQueryable <Version.ValidationResult> GetValidationResults(IQuery query)
{
var ruleResults =
from order in query.For <Order>()
from advertisement in query.For <Order.AddressAdvertisementNonOnTheMap>().Where(x => x.OrderId == order.Id)
select new Version.ValidationResult
{
MessageParams =
new MessageParams(
new Reference <EntityTypeFirmAddress>(advertisement.AddressId),
new Reference <EntityTypeOrder>(order.Id),
new Reference <EntityTypeOrderPositionAdvertisement>(0,
new Reference <EntityTypeOrderPosition>(advertisement.OrderPositionId),
new Reference <EntityTypePosition>(advertisement.PositionId)))
.ToXDocument(),
PeriodStart = order.Begin,
PeriodEnd = order.End,
OrderId = order.Id,
};
return(ruleResults);
}
public override async Task <IEnumerable <TEntity>?> GetAllNonRelatedAsync(IRelated related, IQuery <TDatabaseEntity> query)
{
var results = await _apiRepositoryHelper.DoRequestAsync <EntitiesModel <TEntity> >(_apiRepositoryHelper.CreateRequest(HttpMethod.Post, "all/nonrelated", new RelatedQueryModel(related, query)));
if (results == default)
{
return(Enumerable.Empty <TEntity>());
}
query.HasMoreData(results.MoreDataAvailable);
return(results.Entities.Select(x => x.Entity));
}
public static List <T1> GetPageRecords <T1, T2>(IQuery queryParams, string statisticsCols, out T2 result2) where T1 : new() where T2 : new()
{
var list = GetPageRecords <T1, T2>(queryParams.Table, queryParams.SelectCols, statisticsCols, queryParams.WhereClause, queryParams.GetOrderByClause(), queryParams.PageIndex, queryParams.PageSize, out result2);
return(list);
}
