C# SqlQuery.Where方法代码示例

 public void OnPrepareQuery(IListRequestHandler handler, SqlQuery query)
 {
     var user = (UserDefinition)Authorization.UserDefinition;
     if (!Authorization.HasPermission(PermissionKeys.Tenants))
         query.Where(fldTenantId == user.TenantId);
 }

        public void SubQueryCanBeUsedAsCriteriaUsingVar()
        {
            var query = new SqlQuery()
                .From("ParentTable")
                .Select("ParentColumn");

            query.Where(new Criteria(query.SubQuery()
                .From("SubTable")
                .Take(1)
                .Select("SubColumn")) >= 1);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT ParentColumn FROM ParentTable WHERE " +
                        "((SELECT TOP 1 SubColumn FROM SubTable) >= @p1)"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void OnPrepareQuery(IListRequestHandler handler, SqlQuery query)
        {
            if (ReferenceEquals(null, Target) ||
                handler.Request.EqualityFilter == null ||
                !attr.HandleEqualityFilter)
                return;

            object value;
            if (handler.Request.EqualityFilter.TryGetValue(Target.PropertyName, out value) ||
                handler.Request.EqualityFilter.TryGetValue(Target.Name, out value))
            {
                if (value == null || value as string == "")
                    return;

                var values = new List<object>();

                if (!(value is string) && value is IEnumerable)
                {
                    foreach (var val in (IEnumerable)value)
                        values.Add(itemKeyField.ConvertValue(val, CultureInfo.InvariantCulture));
                }
                else
                {
                    values.Add(itemKeyField.ConvertValue(value, CultureInfo.InvariantCulture));
                }

                if (values.Count > 0)
                {
                    var ls = new Alias(itemKeyField.Fields.TableName, "__ls");

                    query.Where(Criteria.Exists(
                        query.SubQuery()
                            .From(ls)
                            .Select("1")
                            .Where(
                                new Criteria(ls[thisKeyField]) == new Criteria((Field)((IIdRow)handler.Row).IdField) &
                                new Criteria(ls[itemKeyField]).In(values))
                            .ToString()));
                }

                handler.IgnoreEqualityFilter(Target.PropertyName);
                handler.IgnoreEqualityFilter(Target.Name);
            }
        }

        public static Dictionary<Int64, string> GetIdNameDictionary(IDbConnection connection, IIdRow row, StringField nameField,
            IEnumerable<Int64> idList)
        {
            var list = new List<Int64>(idList);
            var dictionary = new Dictionary<Int64, string>();
            if (list.Count <= 0)
                return dictionary;

            list.Sort();

            var theRow = (Row)row;

            Int64[] part = null;
            const int step = 100;

            var i = 0;
            while (i < list.Count)
            {
                var start = i;
                var end = start + step;
                if (end >= list.Count)
                    end = list.Count - 1;

                var len = end - start + 1;
                if (part == null || len != part.Length)
                    part = new Int64[len];

                list.CopyTo(start, part, 0, len);

                var query = new SqlQuery().Select(((Field)row.IdField).Name).Select(nameField.Name).From(theRow.Table);
                query.Where(new Criteria((Field)row.IdField).In(part));

                using (var reader = SqlHelper.ExecuteReader(connection, query))
                    while (reader.Read())
                        dictionary[reader.ToInt64(0).Value] = reader.AsString(1);

                i += step;
            }

            return dictionary;
        }

        public static AuditLogListResponse List(string schema, AuditLogListRequest request)
        {
#if COREFX
            var fld = Dependency.Resolve<IAuditLogRow>();
#else
            var fld = Dependency.Resolve<IAuditLogRow>(schema);
#endif

            Authorization.ValidateLoggedIn();

            var response = new AuditLogListResponse();

            using (var connection = SqlConnections.NewByKey("Default"))
            {
                response.Entities = new List<Row>();

                var row = ((Row)fld).CreateNew();

                if (request.Sort == null ||
                    request.Sort.Length == 0)
                    request.Sort = new SortBy[] { new SortBy(fld.DateField.Name, true) };

                var query = new SqlQuery().From(row)
                    .Select(
                        (Field)fld.IdField,
                        fld.EntityTypeIdField,
                        fld.EntityIdField,
                        fld.ParentTypeIdField,
                        fld.OldParentIdField,
                        fld.NewParentIdField,
                        fld.DateField,
                        (Field)fld.UserIdField,
                        fld.AuditTypeIdField,
                        fld.OldAuditDataField,
                        fld.NewAuditDataField)
                    .OrderBy(
                        (Field)fld.IdField)
                    .ApplySkipTakeAndCount(request.Skip, request.Take, request.ExcludeTotalCount)
                    .ApplySort(request.Sort);

                if (request.EntityTypeId != null &&
                    request.EntityId != null)
                {
                    var pEntityId = query.AddParam(request.EntityId);
                    var pEntityTypeId = query.AddParam(request.EntityTypeId);

                    query.Where(~(
                        ~(
                            new Criteria(0, fld.EntityTypeIdField) == pEntityTypeId &
                            new Criteria(0, fld.EntityIdField) == pEntityId) |
                        ~(
                            new Criteria(0, fld.ParentTypeIdField) == pEntityTypeId &
                            ~(
                                new Criteria(0, fld.OldParentIdField) == pEntityId |
                                new Criteria(0, fld.NewParentIdField) == pEntityId))));
                }
                else
                {
                    if (request.EntityTypeId != null)
                        query.WhereEqual(fld.EntityTypeIdField, request.EntityTypeId);//Convert.ToInt32(request.EntityTypeId.Value));

                    if (request.EntityId != null)
                        query.WhereEqual(fld.EntityIdField, request.EntityId.Value);
                }

                response.TotalCount = query.ForEach(connection, delegate()
                {
                    response.Entities.Add(row.Clone());
                });

                response.SetSkipTakeTotal(query);

                response.IdNameLookups = new Dictionary<EntityType, Dictionary<long, string>>();
                response.FieldTitles = new Dictionary<EntityType, Dictionary<string, string>>();
                response.ForeignEntityTypes = new Dictionary<EntityType, Dictionary<string, string>>();
                response.EntityTitles = new Dictionary<EntityType, string>();

                var lookups = response.IdNameLookups;
                var titles = response.FieldTitles;
                var foreigns = response.ForeignEntityTypes;
                var entities = response.EntityTitles;

                Action<EntityType, Int64> addLookup = (entityType, id) => {

                    Dictionary<long, string> lookup;
                    if (!lookups.TryGetValue(entityType, out lookup))
                    {
                        lookup = new Dictionary<long, string>();
                        lookups[entityType] = lookup;
                    }

                    if (!lookup.ContainsKey(id))
                        lookup[id] = null;
                };

                Action<EntityType, string> addTitle = (entityType, field) =>
                {

                    Dictionary<string, string> lookup;
                    if (!titles.TryGetValue(entityType, out lookup))
//.........这里部分代码省略.........

        internal static string ProcessReplaceFields(string s, Dictionary<string, Field> replaceFields, ISaveRequestHandler handler)
        {
            if (replaceFields == null)
                return s;

            var row = handler.Row;

            // foreign / calculated fields might not be available yet in new row
            // so load them from database 

            // TODO: if referenced foreign fields changed on update, 
            // values might be wrong in before update where we set filename
            // so need to handle update in AfterSave just like create

            if (handler.IsCreate &&
                replaceFields.Values.Any(x => !x.IsTableField()))
            {
                var idField = (Field)(((IIdRow)handler.Row).IdField);

                row = handler.Row.Clone();
                var query = new SqlQuery()
                    .From(row);

                foreach (var field in replaceFields.Values)
                    query.Select(field);

                query.Where(idField == new ValueCriteria(idField.AsObject(row)));

                query.GetFirst(handler.Connection);
            }

            foreach (var p in replaceFields)
            {
                var val = p.Value.AsObject(row);
                string str;

                var colon = p.Key.IndexOf(":");
                if (colon >= 0)
                    str = String.Format("{0:" + p.Key.Substring(colon + 1, p.Key.Length - colon - 2) + "}", val);
                else
                    str = Convert.ToString(val ?? "", CultureInfo.InvariantCulture);

                str = StringHelper.SanitizeFilename(str).Replace('\\', '_').Replace("..", "_");
                if (string.IsNullOrWhiteSpace(str))
                    str = "_";

                while (str.EndsWith("."))
                    str = str.Substring(0, str.Length - 1) + "_";

                s = s.Replace(p.Key, str);
            }

            while (s.IndexOf("//") > 0)
                s = s.Replace("//", "/_/");

            return s;
        }