Join multiple tables when using a generic repository in C#

c# entity-framework-6 linq mysql repository-pattern

Question

I have a .Net Solution with multiple projects for the layers which are as follows.

  • Business (solution folder)
    • Core (project)
    • Interfaces (project)
  • Data (solution folder)
    • Data (project)
  • Presentation (solution folder)
    • AdminPanel (project)
  • Shared (solution folder)
    • Common (project)

I have implemented the generic repository pattern with IoC layer using Unity i.e. Microsoft.Practices.Unity. Everything works great except when I want to do multi table simple or complex joins. I have tried many different ways. I have gone through every single existing thread here on SO regarding joins in repository but none of them are helpful in what I need.

Here's my repository class.

public class Repository<TEntity> : IRepository<TEntity> where TEntity : class, IEntity
    {
        protected BuyatimeshareModel Context { get; private set; }

        public Repository(IContextFactory contextFactory)
            : this(contextFactory.Get())
        {
        }

        protected Repository(BuyatimeshareModel context)
        {
            context.Database.Log = message => { Common.Logging.Log(message); };
            Context = context;
        }
        IDbSet<TEntity> DbSet
        {
            get
            {
                return Context.Set<TEntity>();
            }
        }
        public TEntity Add(TEntity instance)
        {
            DbSet.Add(instance);
            Context.SaveChanges();
            return instance;
        }

        public void Remove(TEntity instance)
        {
            DbSet.Remove(instance);
            Context.SaveChanges();
        }
        public TEntity FindOne(Expression<Func<TEntity, bool>> predicate)
        {
            return DbSet.AsQueryable().FirstOrDefault(predicate);
        }

        public IEnumerable<TEntity> All()
        {
            return DbSet.AsQueryable();
        }

        public IEnumerable<TEntity> Query() { IQueryable<TEntity> query = DbSet; return query.ToList(); }

        public IEnumerable<TEntity> FindAll(Expression<Func<TEntity, bool>> predicate)
        {
            return DbSet.AsQueryable().Where(predicate);
        }

        public int Count()
        {
            return DbSet.AsQueryable().Count();
        }

        public int Count(Expression<Func<TEntity, bool>> predicate)
        {
            return DbSet.AsQueryable().Count(predicate);
        }

        public bool Exists(Expression<Func<TEntity, bool>> predicate)
        {
            return DbSet.AsQueryable().Any(predicate);
        }
    }

Here's my IoC layer.

public class UnityControllerFactory : DefaultControllerFactory
    {
        IUnityContainer container;

        public UnityControllerFactory(IUnityContainer container)
        {
            this.container = container;
        }

        protected override IController GetControllerInstance(RequestContext requestContext, Type controllerType)
        {
            try
            {
                if (controllerType == null)
                    throw new ArgumentNullException("controllerType");

                if (!typeof(IController).IsAssignableFrom(controllerType))
                    throw new ArgumentException(string.Format(
                        "Type requested is not a controller: {0}", controllerType.Name),
                        "controllerType");

                return container.Resolve(controllerType) as IController;
            }
            catch { return null; }
        }

        public static void Configure()
        {
            IUnityContainer container = new UnityContainer();
            /*string connectionString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;*/
            container.RegisterType<IContextFactory, ContextFactory>(new ContainerControlledLifetimeManager())//, new InjectionConstructor(connectionString))
                     .RegisterType<IUnitOfWork, UnitOfWork>(new ContainerControlledLifetimeManager())
                     .RegisterType<IAdminService, AdminService>()

                     .RegisterType(typeof(IRepository<>), typeof(Repository<>));
            ControllerBuilder.Current.SetControllerFactory(new UnityControllerFactory(container));
        }
    }

And finally here's my service layer where I'm trying to query some data from the repository layer.

public class AdminService : IAdminService
    {
        private readonly IRepository<press_releases> pressReleasesRepo;
        private readonly IRepository<tblads> adsRepo;
        private readonly IRepository<tblresorts> resortsRepo;

        public AdminService(IRepository<press_releases> _pressReleasesRepo, IRepository<tblads> _adsRepo, IRepository<tblresorts> _resortsRepo)
        {
            pressReleasesRepo = _pressReleasesRepo;
            adsRepo = _adsRepo;
            resortsRepo = _resortsRepo;
        }
        public List<press_releases> Test()
        {
            var data = pressReleasesRepo.FindAll(p => p.pr_id > 0);
            var data1 =
                (from a in adsRepo.Query()
                    join r in resortsRepo.Query() on a.resort_id equals r.resort_id
                    where a.ad_id == 413
                    select new
                    {
                        OwnerId = a.owner_id,
                        ResortName = r.name,
                        AdId = a.ad_id,
                        AskingPrice = a.askingPriceInt
                    }).ToList();

            var model = data.ToList();
            return model;
        }
    }

Now here we have two queries. data and data1

data is simply querying one single table with a criteria and its returning results as expected. All good here.

But, in data1, it is also technically returning exactly the end result that I want BUT I also have a raw SQL logger in place to see what's happening behind the scenes and what it's doing is to query both ads table and resorts table separately using a select * equivalent statement and then when the results return from both tables, it's applying the joins within memory and returning the result after filtering through the where clause. So basically its scanning through around 100000 rows even though I have join and where clause in place and in the end, it returns one single row with ad id 413.

One thing to note down is that I'm calling Query method of the repository class while doing the join for data1 which is returning an IEnumerable. I was not able to change it to IQueryable because then an exception was being thrown having some message like the linq query has references to different contexts.

Can anyone guide me to modify this code so that I can apply real sql joins between the repositories or if there's a way to modify this code by adding a middle layer to act as a bridge. I need something to go forward from this point. I've been looking everywhere but no avail. I'm sure that I'm not the first one ever in this world who could have this problem. There definitely would be others having similar issue and may be someone who have found a real way.

My tools and technologies are as follows.

  • Visual Studio 2013
  • .Net framework 4.0
  • MySQL Database
  • MySQL for Visual Studio 1.2.4
  • MySQL Connector 6.9.6
  • Entity Framework 6
  • Windows 10 Pro x64

Please let me know if there's anything that I might have missed.

Any help would be greatly appreciated.

Edit:

Here's the ContextFactory class

public class ContextFactory : IContextFactory
    {
        private bool _isDisposed;
        private SomeModel _context;

        public ContextFactory()
        { }

        public SomeModel Get()
        {
            _context = new SomeModel();
            return _context;
        }

        ~ContextFactory()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_isDisposed && disposing && (_context != null))
                _context.Dispose();

            _isDisposed = true;
        }
    }
1
1
8/21/2015 5:50:23 AM

Popular Answer

Here is the method you have in your repository which you are using in your data1.

public IEnumerable<TEntity> Query() { IQueryable<TEntity> query = DbSet; return query.ToList(); }

Whenever you hit '.ToList()' it will hit the database.

If you want to create the query first and then execute then use 'DbSet' property of your repository.

0
8/20/2015 8:39:41 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow