C# Sql.Where方法代码示例

        /// <summary>
        /// Check if there is a content override for this content type and property type.
        /// </summary>
        /// <param name="contentTypeAlias"></param>
        /// <param name="propertyTypeAlias"></param>
        /// <param name="archetypeAlias"></param>
        /// <param name="nodeId"></param>
        /// <param name="type"></param>
        /// <param name="configAlias"></param>
        /// <returns></returns>
        public static bool HasContentOverride(
            string contentTypeAlias,
            string propertyTypeAlias,
            string archetypeAlias = null,
            int? nodeId = null,
            ContentOverrideType type = ContentOverrideType.Config,
            string configAlias = null
            )
        {
            var typeValue = type.ToString();

            var sql = new Sql();
            sql.Select("COUNT(*)")
                .From<ContentOverrideDto>()
                .Where<ContentOverrideDto>(x => x.ContentTypeAlias == contentTypeAlias)
                .Where<ContentOverrideDto>(x => x.PropertyTypeAlias == propertyTypeAlias)
                .Where<ContentOverrideDto>(x => x.Type == typeValue);

            if (!string.IsNullOrWhiteSpace(archetypeAlias))
            {
                sql.Where<ContentOverrideDto>(x => x.ArchetypeAlias == archetypeAlias);
            }

            if (nodeId.HasValue)
            {
                sql.Where<ContentOverrideDto>(x => x.NodeId == nodeId.Value);
            }

            if (type == ContentOverrideType.Config && !string.IsNullOrWhiteSpace(configAlias))
            {
                sql.Where<ContentOverrideDto>(x => x.ConfigAlias == configAlias);
            }

            return Database.ExecuteScalar<int>(sql) > 0;
        }

 public ActionResult Query(IconQueryModel query)
 {
     Sql sql=new Sql();
     if (query.Size != null)
         sql.Where("[email protected]", query.Size.Split('*')[0]).Where("[email protected]", query.Size.Split('*')[1]);
     if (query.BeginCreateTime != null)
         sql.Where("CreateTime>@0", query.BeginCreateTime);
     if (query.EndCreateTime != null)
         sql.Where("CreateTime<@0", query.EndCreateTime);
     PagedList<IconViewModel> result =IconRepository.PagedLists(query.Page, query.Rows, sql);//new PagedList<IconViewModel>(icons, query.Page, query.Rows);
     return new DataGridResult(result, result.TotalItemCount).GetResult();
 } 

 public IEnumerable<Models.Forum> GetForums(int rootId)
 {
     var sql = new Sql();
     sql.Where<Models.Forum>(x => x.ParentId == rootId);
     sql.OrderBy<Models.Forum>(x => x.SortOrder);
     return _databaseContext.Database.Fetch<Models.Forum>(sql);
 }

        public IEnumerable<ProjectContributor> GetContributors(int projectId)
        {
            var sql = new Sql()
                .Select("*")
                .From<ProjectContributor>();

            sql.Where<ProjectContributor>(x => x.ProjectId == projectId);

            return _dbContext.Database.Query<ProjectContributor>(sql);
        }

        public Page<ForumTopicSubscriber> GetTopicSubscriptionsFromMember(int memberId, long take = 50, long page = 1)
        {
            var sql = new Sql()
                .Select("*")
                .From<ForumTopicSubscriber>();

            sql.Where<ForumTopicSubscriber>(x => x.MemberId == memberId);

            return _databaseContext.Database.Page<ForumTopicSubscriber>(page, take, sql);
        }

        public Page<ForumTopicSubscriber> GetTopicSubscriptionsFromMember(int memberId, long take = 50, long page = 1)
        {
            var sql = new Sql()
                .Select("*")
                .From<ForumTopicSubscriber>();

            sql.Where<ForumTopicSubscriber>(x => x.MemberId == memberId);
            sql.OrderBy<ForumTopicSubscriber>(x => x.TopicId); // have to order by something when paging

            return _databaseContext.Database.Page<ForumTopicSubscriber>(page, take, sql);
        }

 public IEnumerable<JobTemplate> GetAllJobTemplate(int? companyID = null)
 {
     var query = new Sql().Select("*").From("JobsplusJobTemplates");
     if (companyID.HasValue)
     {
         // DKO: doplňující INFO
         // jedná se obecnou šablonu viditelnou pro všechny nebo pro tuto firmu
         // jedná se o šablonu, kterou si založila přihlášená firma pro své vlastní účely
         /*
         query = query.Where<JobTemplate>(x =>
             (x.IsGeneralTemplate && (x.IsVisibleForAll || x.VisibleForCompanyIds.Equals(companyID.Value.ToString()) || x.VisibleForCompanyIds.Contains("," + companyID.Value.ToString()))) ||
             (x.CreatedByCompanyId.HasValue && x.CreatedByCompanyId.Value.Equals(companyID.Value))
             ).OrderBy("Name");
          */
         var sQuery = @"(IsGeneralTemplate = 1 AND (IsVisibleForAll = 1 OR (VisibleForCompanyIds = @0 OR VisibleForCompanyIds LIKE '%,@0%')))
             OR (CreatedByCompanyId IS NOT NULL AND CreatedByCompanyId = @0)";
         query.Where(sQuery, companyID.Value.ToString());
     }
     return db.Fetch<JobTemplate>(query);
 }

        public void Can_Verify_Property_Collection_Query()
        {
            var versionId = new Guid("2b543516-a944-4ee6-88c6-8813da7aaa07");
            var id = 1050;

            var expected = new Sql();
            expected.Select("*");
            expected.From("[cmsPropertyData]");
            expected.InnerJoin("[cmsPropertyType]").On("[cmsPropertyData].[propertytypeid] = [cmsPropertyType].[id]");
            expected.Where("[cmsPropertyData].[contentNodeId] = 1050");
            expected.Where("[cmsPropertyData].[versionId] = '2b543516-a944-4ee6-88c6-8813da7aaa07'");

            var sql = new Sql();
            sql.Select("*")
                .From<PropertyDataDto>()
                .InnerJoin<PropertyTypeDto>()
                .On<PropertyDataDto, PropertyTypeDto>(left => left.PropertyTypeId, right => right.Id)
                .Where<PropertyDataDto>(x => x.NodeId == id)
                .Where<PropertyDataDto>(x => x.VersionId == versionId);

            Assert.That(sql.SQL, Is.EqualTo(expected.SQL));

            Console.WriteLine(sql.SQL);
        }

        /// <summary>
        /// Returns an in-memory collection of topics that a given member has participated in
        /// </summary>
        /// <param name="memberId"></param>
        /// <param name="ignoreSpam"></param>
        /// <param name="maxCount"></param>
        /// <returns></returns>
        public IEnumerable<ReadOnlyTopic> GetLatestTopicsForMember(int memberId, bool ignoreSpam = true, int maxCount = 100)
        {
            var sql = new Sql().Select("TOP " + maxCount + " " +
                                       @"forumTopics.*, u1.[text] as LastReplyAuthorName, u2.[text] as AuthorName,
            forumComments.body as commentBody, forumComments.created as commentCreated, forumComments.haschildren,
            forumComments.id as commentId, forumComments.isSpam as commentIsSpam, forumComments.memberId as commentMemberId, forumComments.parentCommentId,
            forumComments.position, forumComments.score, forumComments.topicId")
                .From("forumTopics")
                .LeftOuterJoin("forumComments").On("forumTopics.id = forumComments.topicId")
                .LeftOuterJoin("umbracoNode u1").On("(forumTopics.latestReplyAuthor = u1.id AND u1.nodeObjectType = '39EB0F98-B348-42A1-8662-E7EB18487560')")
                .LeftOuterJoin("umbracoNode u2").On("(forumTopics.memberId = u2.id AND u2.nodeObjectType = '39EB0F98-B348-42A1-8662-E7EB18487560')")
                .Where<Topic>(topic => topic.LatestReplyAuthor == memberId || topic.MemberId == memberId);

            if (ignoreSpam)
            {
                sql.Where<Topic>(x => x.IsSpam != true);
                sql.Where("forumComments.id IS NULL OR (forumComments.isSpam <> 1)");
            }

            sql
                .OrderByDescending<Topic>(x => x.Updated)
                .OrderByDescending<Comment>(comment => comment.Created);

            var result = _databaseContext.Database.Fetch<ReadOnlyTopic, ReadOnlyComment, ReadOnlyTopic>(
                new TopicCommentRelator().Map,
                sql);

            return result;
        }

        /// <summary>
        /// Return all the content override values for this content type.
        /// </summary>
        /// <param name="contentTypeAlias"></param>
        /// <param name="type"></param>
        /// <param name="configAlias"></param>
        /// <returns></returns>
        public static List<ContentOverrideDto> GetContentOverrides(
            string contentTypeAlias,
            ContentOverrideType type = ContentOverrideType.Config,
            string configAlias = null
            )
        {
            var typeValue = type.ToString();

            var sql = new Sql();
            sql.Select("*")
                .From<ContentOverrideDto>()
                .Where<ContentOverrideDto>(x => x.ContentTypeAlias == contentTypeAlias)
                .Where<ContentOverrideDto>(x => x.Type == typeValue);

            if (type == ContentOverrideType.Config && !string.IsNullOrWhiteSpace(configAlias))
            {
                sql.Where<ContentOverrideDto>(x => x.ConfigAlias == configAlias);
            }

            return Database.Fetch<ContentOverrideDto>(sql);
        }

 public PartialViewResult GetIconTab(int width, int height, QueryModel query)
 {
     Sql sql = new Sql();
     sql.Where("[email protected]", width).Where("[email protected]", height);
     PagedList<IconViewModel> result = IconRepository.PagedLists(query.Page, query.Rows,sql);
     IconListViewModel iconListViewModel = new IconListViewModel(width, height, result);
     return PartialView("IconsControl/Tab", iconListViewModel);
 } 

 /// <summary>
 /// Získá všechny ÚP
 /// </summary>
 /// <returns></returns>
 public IEnumerable<EmployDepartment> GetAll(int? regionId = null)
 {
     var query = new Sql().Select("*").From("JobsplusEmployDepartments");
     if (regionId.HasValue) query.Where<EmployDepartment>(i => i.RegionId == regionId.Value);
     return DatabaseContext.Database.Fetch<EmployDepartment>(query);
 }

        public OrdersInfo Get(int pageNumber, string from, string till, string manager, string status, string searchString, string orderBy, string orderDirection, int discount)
        {
            DateTime fromDate, tillDate;
            var fromInUse = DateTime.TryParseExact(from, "yyyy-MM-dd", null, DateTimeStyles.AssumeLocal, out fromDate);
            var tillInUse = DateTime.TryParseExact(till, "yyyy-MM-dd", null, DateTimeStyles.AssumeLocal, out tillDate);
            var managerId = Int32.Parse(manager);
            var statusId = Int32.Parse(status);

            var query = new Sql();
            if (fromInUse)
            {
                query.Where("DateCreated >= @0", fromDate);
            }
            if (tillInUse)
            {
                query.Where("DateCreated <= @0", tillDate);
            }
            if (managerId == 0)
            {
                query.Where("ManagerId is NULL");
            }
            else if (managerId > 0)
            {
                query.Where("ManagerId = @0", managerId);
            }
            if (statusId == 0)
            {
                query.Where("Status is NULL");
            }
            else if (statusId > 0)
            {
                var newStatusNode = OrderModel.StatusCache.First(x => x.Value.Id == statusId).Value;
                query.Where("Status = @0", newStatusNode.GetPropertyValue<string>("dbValue"));
            }
            query.OrderBy(orderBy + " " + (orderDirection == "asc" ? "" : "desc"));

            var orderDtos = ApplicationContext.Current.DatabaseContext.Database.Fetch<OrderDto>(query); //заказы с учетом возможной фильтрации по дате, менеджеру и статусу, но без учета поисковой строки
            var orderModels = from orderDto in orderDtos select new OrderModel(orderDto, !String.IsNullOrEmpty(searchString)); //заказы же, но уже в виде "моделей" (с максимумом данных)
            if (!String.IsNullOrEmpty(searchString))
            {
                searchString = searchString.ToLower();

                var searchPhone = '+' + searchString.TrimStart('+'); //такая операция с плюсами для того, чтобы можно было искать и с плюсом, и без
                var ordersByMobilePhone = orderModels.Where(orderModel => orderModel.customerPhone.StartsWith(searchPhone));

                var searchNameParts = Regex.Matches(searchString, @"\b[A-Za-zА-ЯЁа-яё]+\b").Cast<Match>().Select(x => x.Value);
                var ordersByName = orderModels.Where(orderModel =>
                    {
                        return searchNameParts.All(searchNamePart =>
                            orderModel.customerFirstName.ToLower().StartsWith(searchNamePart) ||
                            orderModel.customerSecondName.ToLower().StartsWith(searchNamePart) ||
                            orderModel.customerLastName.ToLower().StartsWith(searchNamePart)
                        );
                    }
                ); //вариант фильтрации, когда необходимо попадание каждого словесного компонента поисковой строки в какой-нибудь компонент имени
                /*var ordersByName = orderModels.Where(orderModel =>
                    {
                        var firstName = orderModel.customerFirstName.ToLower();
                        var secondName = orderModel.customerSecondName.ToLower();
                        var lastName = orderModel.customerLastName.ToLower();
                        var found = searchNameParts.Any(x => firstName.StartsWith(x)) ||
                            searchNameParts.Any(x => secondName.StartsWith(x)) ||
                            searchNameParts.Any(x => lastName.StartsWith(x));
                        return found;
                    }
                );*/ //вариант фильтрации, когда достаточно попадания компонентов поисковой строки в один компонент имени

                var ordersByOrganization = orderModels.Where(orderModel => Regex.IsMatch(orderModel.customerOrganization.ToLower(), @"\b" + searchString));

                var ordersByProduct = orderModels.Where(orderModel => orderModel.items.Any(item => Regex.IsMatch(item.name.ToLower(), @"\b" + searchString)));

                orderModels = ordersByMobilePhone
                    .Concat(ordersByName)
                    .Concat(ordersByOrganization)
                    .Concat(ordersByProduct)
                    .Distinct()
                    .ToArray();
            }

            var totalPages = (int)Math.Ceiling((float)orderModels.Count() / PAGE_SIZE);
            if (pageNumber > totalPages) pageNumber = totalPages;
            return new OrdersInfo
            {
                items = orderModels.Skip(PAGE_SIZE * (pageNumber - 1)).Take(PAGE_SIZE),
                totalPages = totalPages
            };
        }

 /// <summary>
 /// Získá všechny definice dotace dle filtru.
 /// </summary>
 /// <param name="grantId">Id dotace</param>
 /// <returns></returns>
 public IEnumerable<GrantDefinition> GetAll(int? grantId = null)
 {
     var query = new Sql().Select("*").From("JobsplusGrantDefinitions");
     if (grantId.HasValue) query.Where<GrantDefinition>(item => item.GrantId == grantId.Value);
     return DatabaseContext.Database.Fetch<GrantDefinition>(query);
 }