public IList<Auction.Domain.Raffle> GetByEvent(long eventId,
                                        ref IAuctionTransaction trans)
        {
            using (var records = new RaffleCollection())
            {
                var filter = new PredicateExpression(RaffleFields.EventId == eventId);

                if (trans != null)
                {
                    trans.Add(records);
                }
                records.GetMulti(filter);
                return records.Select(
                        r =>
                        new Raffle()
                            {
                                CreatedBy = r.CreatedBy,
                                EventId = r.EventId,
                                Id = r.Id,
                                Name = r.Name,
                                Revenue = r.Total,
                                UpdatedBy = (long) r.UpdatedBy
                            }).ToList();
            }
        }

        public static int Add(int seriesId, int season)
        {
            var entity = new SeasonEntity { SeasonNumber = season, SeriesId = seriesId };
            var filter = new PredicateExpression { SeasonFields.SeriesId == seriesId, SeasonFields.SeasonNumber == season };

            return Database.AddOrUpdateEntity(entity, filter, false).SeasonId;
        }

        public static bool HasStream(int fileId)
        {
            var filter = new PredicateExpression { FileVideoStreamFields.FileId == fileId };
            var entity = Database.GetEntityCollection<FileVideoStreamEntity>(new RelationPredicateBucket(filter));

            return entity.Any();
        }

        public static bool HasEpisodeAttached(int fileId)
        {
            var filter = new PredicateExpression { EpisodeToFileFields.FileId == fileId };
            var entity = Database.GetEntityCollection<EpisodeToFileEntity>(new RelationPredicateBucket(filter));

            return entity.Any();
        }

 public ProviderQueryExpression(
     IEnumerable<ProviderPropertyExpression> providerProperties,
     ProjectionExpression projection,
     PredicateExpression predicate,
     SortExpressionCollectionExpression sort)
     : this(new ProviderPropertiesExpression(providerProperties), projection, predicate, sort)
 { }

 public Auction.Domain.Package Get(long Id, ref IAuctionTransaction trans)
 {
     using (var records = new PackageCollection())
     {
         var filter = new PredicateExpression(PackageFields.Id == Id);
         if (trans != null)
         {
             trans.Add(records);
         }
         records.GetMulti(filter, 1);
         var r = records.FirstOrDefault();
         return new Package()
                    {
                        BidderId = r.BidderId,
                        CategoryId = r.CategoryId,
                        ClosedOutBy = r.ClosedOutBy,
                        Code = r.Code,
                        CreatedBy = r.CreatedBy,
                        EndingBid = r.EndingBid,
                        EventId = r.EventId,
                        Id = r.Id,
                        Name = r.Name,
                        Notes = r.Notes,
                        Paid = (bool) r.Paid,
                        StartingBid = r.StartingBid,
                        UpdatedBy = r.UpdatedBy
                    };
     }
 }

 public IList<Auction.Domain.Package> GetByCategory(long categoryId,
                            ref IAuctionTransaction trans)
 {
     using (var records = new PackageCollection())
     {
         var filter = new PredicateExpression(PackageFields.CategoryId == categoryId);
         if (trans != null)
         {
             trans.Add(records);
         }
         records.GetMulti(filter);
         return records.Select(r => new Package()
         {
             BidderId = r.BidderId,
             CategoryId = r.CategoryId,
             ClosedOutBy = r.ClosedOutBy,
             Code = r.Code,
             CreatedBy = r.CreatedBy,
             EndingBid = r.EndingBid,
             EventId = r.EventId,
             Id = r.Id,
             Name = r.Name,
             Notes = r.Notes,
             Paid = (bool)r.Paid,
             StartingBid = r.StartingBid,
             UpdatedBy = r.UpdatedBy
         }).ToList();
     }
 }

 public IList<Auction.Domain.Event> GetByAccount(long accountId,
                           ref IAuctionTransaction trans)
 {
     using (var records = new AuctionEventCollection())
     {
         var filter = new PredicateExpression(AuctionEventFields.AccountId == accountId);
         if (trans != null)
         {
             trans.Add(records);
         }
         records.GetMulti(filter, 0);
         return
             records.Select(
                 b =>
                 new Event()
                     {
                         Id = b.Id,
                         Date = b.Date,
                         Name = b.Name,
                         AccountId = b.AccountId,
                         CreatedBy = b.CreatedBy,
                         Locked = b.Locked,
                         Notes = b.Notes,
                         UpdatedBy = b.UpdatedBy
                     }).ToList();
     }
 }

        public Event Get(long Id, ref IAuctionTransaction trans)
        {
            using (var records = new AuctionEventCollection())
            {
                var filter = new PredicateExpression(AuctionEventFields.Id == Id);

                if (trans != null)
                {
                    trans.Add(records);
                }
                records.GetMulti(filter, 1);
                var b = records.ToList().FirstOrDefault();
                return new Event()
                {
                    Id = Id,
                    Date = b.Date,
                    Name = b.Name,
                    AccountId = b.AccountId,
                    CreatedBy = b.CreatedBy,
                    Locked = b.Locked,
                    Notes = b.Notes,
                    UpdatedBy = b.UpdatedBy
                };
            }
        }

 public string GetTodayVisit()
 {
     WebTrackerCollection trackers = new WebTrackerCollection();
     PredicateExpression filter = new PredicateExpression();
     filter.Add(WebTrackerFields.VisitTime == DateTime.Parse(DateTime.Now.ToShortDateString()));
     trackers.GetMulti(filter);
     return trackers.Count.ToString();
 }

        /// <summary>
        /// Does the search using MS Full text search
        /// </summary>
        /// <param name="searchString">Search string.</param>
        /// <param name="forumIDs">Forum Ids of forums to search into.</param>
        /// <param name="orderFirstElement">Order first element setting.</param>
        /// <param name="orderSecondElement">Order second element setting.</param>
        /// <param name="forumsWithThreadsFromOthers">The forums with threads from others.</param>
        /// <param name="userID">The userid of the calling user.</param>
        /// <param name="targetToSearch">The target to search.</param>
        /// <returns>
        /// TypedList filled with threads matching the query.
        /// </returns>
        public static SearchResultTypedList DoSearch(string searchString, List<int> forumIDs, SearchResultsOrderSetting orderFirstElement,
            SearchResultsOrderSetting orderSecondElement, List<int> forumsWithThreadsFromOthers, int userID, SearchTarget targetToSearch)
        {
            // the search utilizes full text search. It performs a CONTAINS upon the MessageText field of the Message entity.
            string searchTerms = PrepareSearchTerms(searchString);
            bool searchMessageText = (targetToSearch == SearchTarget.MessageText) || (targetToSearch == SearchTarget.MessageTextAndThreadSubject);
            bool searchSubject = (targetToSearch == SearchTarget.ThreadSubject) || (targetToSearch == SearchTarget.MessageTextAndThreadSubject);
            if(!(searchSubject || searchMessageText))
            {
                // no target specified, select message
                searchMessageText = true;
            }

            PredicateExpression searchTermFilter = new PredicateExpression();
            if(searchMessageText)
            {
                // Message contents filter
                searchTermFilter.Add(new FieldCompareSetPredicate(ThreadFields.ThreadID, MessageFields.ThreadID,
                                    SetOperator.In, new FieldFullTextSearchPredicate(MessageFields.MessageText, FullTextSearchOperator.Contains, searchTerms)));
            }
            if(searchSubject)
            {
                // Thread subject filter
                if(searchMessageText)
                {
                    searchTermFilter.AddWithOr(new FieldFullTextSearchPredicate(ThreadFields.Subject, FullTextSearchOperator.Contains, searchTerms));
                }
                else
                {
                    searchTermFilter.Add(new FieldFullTextSearchPredicate(ThreadFields.Subject, FullTextSearchOperator.Contains, searchTerms));
                }
            }
            IPredicateExpression mainFilter = searchTermFilter
                                                    .And(ForumFields.ForumID == forumIDs)
                                                    .And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userID));

            ISortExpression sorter = new SortExpression();
            // add first element
            sorter.Add(CreateSearchSortClause(orderFirstElement));
            if(orderSecondElement != orderFirstElement)
            {
                sorter.Add(CreateSearchSortClause(orderSecondElement));
            }

            SearchResultTypedList results = new SearchResultTypedList(false);

            try
            {
                // get the data from the db.
                results.Fill(500, sorter, false, mainFilter);
            }
            catch
            {
                // probably an error with the search words / user error. Swallow for now, which will result in an empty resultset.
            }

            return results;
        }

 /// <summary>Retrieves in the calling AuditActionCollection object all AuditActionEntity objects which are related via a relation of type 'm:n' with the passed in RoleEntity.</summary>
 /// <param name="containingTransaction">A containing transaction, if caller is added to a transaction, or null if not.</param>
 /// <param name="collectionToFill">Collection to fill with the entity objects retrieved</param>
 /// <param name="maxNumberOfItemsToReturn"> The maximum number of items to return with this retrieval query. When set to 0, no limitations are specified.</param>
 /// <param name="sortClauses">The order by specifications for the sorting of the resultset. When not specified, no sorting is applied.</param>
 /// <param name="entityFactoryToUse">The EntityFactory to use when creating entity objects during a GetMulti() call.</param>
 /// <param name="roleInstance">RoleEntity object to be used as a filter in the m:n relation</param>
 /// <param name="prefetchPathToUse">the PrefetchPath which defines the graph of objects to fetch.</param>
 /// <param name="pageNumber">The page number to retrieve.</param>
 /// <param name="pageSize">The page size of the page to retrieve.</param>
 /// <returns>true if succeeded, false otherwise</returns>
 public bool GetMultiUsingRolesWithAuditAction(ITransaction containingTransaction, IEntityCollection collectionToFill, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IEntity roleInstance, IPrefetchPath prefetchPathToUse, int pageNumber, int pageSize)
 {
     RelationCollection relations = new RelationCollection();
     relations.Add(AuditActionEntity.Relations.RoleAuditActionEntityUsingAuditActionID, "RoleAuditAction_");
     relations.Add(RoleAuditActionEntity.Relations.RoleEntityUsingRoleID, "RoleAuditAction_", string.Empty, JoinHint.None);
     IPredicateExpression selectFilter = new PredicateExpression();
     selectFilter.Add(new FieldCompareValuePredicate(roleInstance.Fields[(int)RoleFieldIndex.RoleID], ComparisonOperator.Equal));
     return this.GetMulti(containingTransaction, collectionToFill, maxNumberOfItemsToReturn, sortClauses, entityFactoryToUse, selectFilter, relations, prefetchPathToUse, pageNumber, pageSize);
 }

        public CategoryCollection GetMainCategories()
        {
            CategoryCollection categories = new CategoryCollection();
            PredicateExpression filter = new PredicateExpression();
            filter.Add(new FieldCompareNullPredicate(CategoryFields.BaseCategoryId));
            categories.GetMulti(filter);

            return categories;
        }

        /// <summary>
        /// Returns valid entities which are valid for given moment in time.
        /// Valid entities have:
        /// 1. Begining is not NULL AND is LESS than given moment in time.
        /// 2. End is NULL OR is GREATER OR EQUAL than given moment in time.
        /// </summary>
        public static PredicateExpression FilterValidEntities(DateTime momentInTime,
            EntityField2 validFromDateTimeField,
            EntityField2 validToDateTimeField)
        {
            PredicateExpression predicateExpression = new PredicateExpression();
            predicateExpression.Add(validFromDateTimeField != DBNull.Value & validFromDateTimeField <= momentInTime);
            predicateExpression.AddWithAnd(validToDateTimeField == DBNull.Value | validToDateTimeField >= momentInTime);

            return predicateExpression;
        }

        public static void Add(int artworkId, int roleId)
        {
            var entity = new ArtworkToRoleEntity();
            entity.ArtworkId = artworkId;
            entity.RoleId = roleId;

            var filter = new PredicateExpression();
            filter.Add(ArtworkToRoleFields.ArtworkId == artworkId);
            filter.Add(ArtworkToRoleFields.RoleId == roleId);
            Database.AddOrUpdateEntity(entity, filter, false);
        }

 public void Delete(long Id, ref IAuctionTransaction trans)
 {
     using (var records = new CategoryCollection())
     {
         var filter = new PredicateExpression(CategoryFields.Id == Id);
         if (trans != null)
         {
             trans.Add(records);
         }
         records.DeleteMulti(filter);
     }
 }

 public bool EventExists(long id, ref IAuctionTransaction trans)
 {
     using (var records = new AuctionEventCollection())
     {
         var filter = new PredicateExpression(AuctionEventFields.Id == id);
         if (trans != null)
         {
             trans.Add(records);
         }
         return records.GetDbCount(filter) > 0;
     }
 }

 public MembershipEntity GetMember(string uName, string password)
 {
     MembershipCollection memberships = new MembershipCollection();
     PredicateExpression filter = new PredicateExpression();
     filter.AddWithAnd(MembershipFields.UserName == uName);
     filter.AddWithAnd(MembershipFields.Password == Business.Encryption.SHA1Encryption.EncryptMessage(password));
     filter.AddWithAnd(MembershipFields.Status == true);
     memberships.GetMulti(filter);
     if (memberships.Count > 0)
         return memberships.FirstOrDefault();
     else
         return null;
 }

        public SelectList subjectAttributes(object selObjId)
        {
            if (m_subjectAttributes == null)
            {
                m_subjectAttributes = new AttributeCollection();
                RelationCollection rels = new RelationCollection(AttributeEntity.Relations.ContextEntityUsingContextId);
                PredicateExpression pe = new PredicateExpression(ContextFields.Name == "subject");
                SortExpression se = new SortExpression(AttributeFields.Name | SortOperator.Ascending);
                m_subjectAttributes.GetMulti(pe, 0, se, rels);
            }

            SelectList selList = new SelectList(m_subjectAttributes, "Id", "Name", selObjId);
            return selList;
        }

        public IList<Category> GetByAccount(long accountId, ref IAuctionTransaction trans)
        {
            using (var records = new CategoryCollection())
            {
                var filter = new PredicateExpression(CategoryFields.AccountId == accountId);

                if (trans != null)
                {
                    trans.Add(records);
                }
                records.GetMulti(filter, 0);
                return records.ToList().Select(c => new Category() { Id = c.Id, Name = c.Name, AccountId = c.AccountId }).ToList();
            }
        }