All records retrieved from DB

[MichaelH]

Hi,
We have applied repository pattern in our project so we have wrapped PgSqlDataProvider in data factory.

Code: Select all

[ProviderAttribute(typeof(PgSqlDataProvider))]
    public class PostgreDataContext : DataContext
    {
        #region Constants
        public const string ConnectionString = "KTDConnectionString";
        #endregion

        #region Private Variables
        #endregion

        #region .ctor
        public PostgreDataContext(IDbConnection connection)
            : base(connection)
        { }

        public PostgreDataContext(string connectionString)
            : base(connectionString)
        { }

        public PostgreDataContext()
            : base(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString)
        { }
        #endregion

        #region Properties
        #endregion

        #region Public Methods
        #endregion

        #region Protected Methods
        #endregion

        #region Private Methods
        #endregion

Here is the actual factory class:

Code: Select all

public class DataContextFactory : IDataContextFactory
    {
        #region Constants
        #endregion

        #region Private Variables
        private string connectionString;
        private IDbConnection connection;
        private PostgreDataContext postgreDataContext;
        #endregion

        #region .ctor
        public DataContextFactory(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public DataContextFactory(IDbConnection connection)
        {
            this.connection = connection;
        }

        public DataContextFactory()
        { }
        #endregion

        #region Properties
        public DataContext Context
        {
            get
            {
                if (postgreDataContext == null)
                {
                    if (!string.IsNullOrEmpty(connectionString))
                        postgreDataContext = new PostgreDataContext(connectionString);
                    else if (connection != null)
                        postgreDataContext = new PostgreDataContext(connection);
                    try
                    {
                        postgreDataContext = new PostgreDataContext();
                    }
                    catch (ArgumentNullException)
                    {
                        throw new MissingFieldException(PostgreDataContext.ConnectionString);
                    }
                }

                return postgreDataContext;
            }
        }

        #endregion

        #region Public Methods
        #endregion

        #region Protected Methods
        #endregion

        #region Private Methods
        #endregion

    }

Here is the actual repository:

Code: Select all

public class Repository : IDisposable, IRepository where T : class, new()
    {
        #region Constants
        #endregion

        #region Private Variables
        private IDataContextFactory dataContextFactory;
        private Table table;
        private bool disposed;
        private string name;
        #endregion

        #region .ctor
        public Repository(IDataContextFactory dataContextFactory)
        {
            if (dataContextFactory == null)
                throw new ArgumentNullException("dataContextFactory");

            this.dataContextFactory = dataContextFactory;
        }

        public Repository(IDataContextFactory dataContextFactory, string name)
        {
            if (dataContextFactory == null)
                throw new ArgumentNullException("dataContextFactory");

            this.dataContextFactory = dataContextFactory;
            this.name = name;
        }
        #endregion

        #region .dctor
        ~Repository()
        {
            Dispose(false);
        }
        #endregion

        #region Properties
        private string PrimaryKey { get { return TableMetadata.RowType.IdentityMembers[0].Name; } }
        private Table Table { get { if (table == null) table = dataContextFactory.Context.GetTable(); return table; } }
        private MetaTable TableMetadata { get { return dataContextFactory.Context.Mapping.GetTable(typeof(T)); } }
        private MetaType ClassMetadata { get { return dataContextFactory.Context.Mapping.GetMetaType(typeof(T)); } }

        protected IDataContextFactory DataContextFactory { get { return dataContextFactory; } }

        public string Name
        {
            get
            {
                if (!string.IsNullOrEmpty(name))
                    return name;

                return name = GetType().ToString() + Guid.NewGuid().ToString();
            }
        }
        #endregion

        #region Public Methods
        #region IRepository Members
        public IEnumerable All()
        {
            return Table;
        }

        public IEnumerable FindAll(Func expression, Func order = null, bool descending = false)
        {
            var query = Table.Where(expression);

            if (order != null)
            {
                if (!descending)
                    return query.OrderBy(order).ToList();
                else
                    return query.OrderByDescending(order).ToList();
            }

            return query.ToList();
        }

        public T Single(Func expression)
        {
            return Table.Single(expression);
        }

        public T First(Func expression)
        {
            return Table.First(expression);
        }

        public virtual void MarkAsDeleted(T obj)
        {
            Table.DeleteOnSubmit(obj);
        }

        public void Save()
        {
            Table.Context.SubmitChanges();
        }

        public virtual T CreateInstance()
        {
            T obj = Activator.CreateInstance();
            Table.InsertOnSubmit(obj);

            return obj;
        }

        public void AttachDisconnected(T obj, Func expression)
        {
            var originalObj = Single(expression);
            originalObj = new Mapper().MapToEntity(obj, originalObj, true);
        }

        public void AttachDisconnected(T obj)
        {
            Expression dynamiclambda = null;

            var type = typeof(T);
            var parameter = Expression.Parameter(type, type.Name);

            var props = obj.GetType().GetProperties();
            for (int index = 0; index > predicate = Expression.Lambda>(dynamiclambda, parameter);
            Func compiled = predicate.Compile();
            var originalObj = First(compiled);
        }

        public void Attach(T obj, T original = null, bool modified = false)
        {
            if (!modified && original == null)
                Table.Attach(obj);
            else if (original != null)
                Table.Attach(obj, original);
            else
                Table.Attach(obj, modified);
        }

        public void AttachAll(IList ObjectsList)
        {
            Table.AttachAll(ObjectsList);
        }
        #endregion

        #region IDisposable Members
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion
        #endregion

        #region Protected Methods
        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                    dataContextFactory.Context.Dispose();
            }

            disposed = true;
        }

        #endregion

        #region Private Methods
        private bool AcceptedType(object obj)
        {
            if (obj is int
                || obj is string
                || obj is decimal
                || obj is double
                || obj is float
                || obj is long
                || obj is bool
                || obj is byte
                || obj is sbyte
                || obj is uint
                || obj is ulong
                || obj is short
                || obj is ushort
                || obj is char
                || obj is DateTime)
                return true;

            return false;
        }
        #endregion
    }

Here it is the test method:

Code: Select all

public TestRepository()
{
DataContextFactory dataContextFactory = new DataContextFactory();
MediaRepository mediaRepository = new MediaRepository(dataContextFactory);

var query = mediaRepository.FindAll(m=>m.MediaId == 99);
}

Here is the Media class:

Code: Select all

[DataContract]
    [Table(Name = "Media")]
    public class MediaDTO : BaseDTO
    {
        #region Constants
        private EntityRef createdByUser;
        private EntityRef updatedByUser;
        #endregion

        #region Private Variables
        #endregion

        #region .ctor
        #endregion

        #region Properties
        [DataMember]
        [Column]
        [NotNullValidator(MessageTemplateResourceName = "NotNullValidatorMessage", MessageTemplateResourceType = typeof(ValidationMessages))]
        public int ContentCodeId { get; set; }

        [Column(Name = "createdbyuser")]
        private int CreatedByUserId { get; set; }

        [DataMember]
        [Association(Storage = "createdByUser", ThisKey = "CreatedByUserId", IsForeignKey = true)]
        [NotNullValidator(MessageTemplateResourceName = "NotNullValidatorMessage", MessageTemplateResourceType = typeof(ValidationMessages))]
        public UserDTO CreatedByUser { get { return createdByUser.Entity; } set { createdByUser.Entity = value; CreatedByUserId = value.UserId; } }

        [DataMember]
        [Column]
        public string Description { get; set; }

        [DataMember]
        [Column]
        public bool External { get; set; }

        [DataMember]
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int MediaId { get; protected set; }

        [DataMember]
        [Column]
        [StringLengthValidator(1, RangeBoundaryType.Inclusive, 145, RangeBoundaryType.Inclusive,
            MessageTemplateResourceName = "StringLengthValidatorMessage",
            MessageTemplateResourceType = typeof(ValidationMessages))]
        [NotNullValidator(MessageTemplateResourceName = "NotNullValidatorMessage", MessageTemplateResourceType = typeof(ValidationMessages))]
        public string Name { get; set; }

        [DataMember]
        [Column]
        [StringLengthValidator(1, RangeBoundaryType.Inclusive, 2000, RangeBoundaryType.Inclusive,
            MessageTemplateResourceName = "StringLengthValidatorMessage",
            MessageTemplateResourceType = typeof(ValidationMessages))]
        [NotNullValidator(MessageTemplateResourceName = "NotNullValidatorMessage", MessageTemplateResourceType = typeof(ValidationMessages))]
        public string Path { get; set; }

        [DataMember]
        [Association(Storage = "updatedByUser", ThisKey = "UpdatedByUserId", IsForeignKey = true)]
        public UserDTO UpdatedByUser { get { return updatedByUser.Entity; } set { updatedByUser.Entity = value; if (value != null) UpdatedByUserId = value.UserId; } }

        [Column(Name = "updatedbyuser")]
        private int UpdatedByUserId { get; set; }
        #endregion

        #region Public Methods
        #endregion

        #region Protected Methods
        #endregion

        #region Private Methods
        #endregion
    }

The Media table from our DB contains over 1 million records. The issue that we encounter is the following one: each time the test query is executed the final result has only one entry which it is correct. But, the query takes over 30 seconds to execute and based on the PostgreSQL profiling we determined that the Where clause it is not applied when data is fetched, instead is applied on the fetched in memory data.

[StanislavK]

Apparently, the problem is that the overload of the Where extension method used in the sample works with (and returns) IEnumerable instead of IQueryable. Because of this, the Table variable is enumerated before applying the Where conditions.

To resolve the problem, you have to specify that the Where method works with IQueryable. To do so, you can, e.g., change the type of the 'expression' parameter to System.Linq.Expressions.Expression>.

Please tell us if this helps.