Do complex queries belong in business layer, or data layer?



  • So, I'm refactoring my clusterfuck of an application. The company mandates having the business logic separated from the data logic, which I agree with (it would also be nice if they held to it themselves, but hey...). So my DAL lives in a MyApp.DAL project, my BL lives in a MyApp.BL project, my UI... you get the drill.

    Now, I'm using Entity Framework for my data layer. And because the database schema is very much not constant, can change under me at a moment's notice, I have very little control over it and occasionally I need to call out to other databases which are totally off-limits, I'm using a database-first .edmx model which gets an Update From Database once in a while. (By the way, it's EF4.0 if anybody wonders - yep, welcome to 5 years ago...)

    Until now, I've kept all the more or less complex queries in the data layer hidden behind repositories - in DAL, I'd have a BusinessThingiesDAORepository with several GetInterestingThingies()-like methods, returning lists of entities. Then, in business layer, I'd have BusinessThingiesRepository with the same set of methods basically proxying the calls and transforming the entities into business objects I need further in the application.

    Now that seemed a) a little redundant (for every logical object, I need to create a DA repository, a method in said repository doing the actual work, a BL repository, and a proxy method), and b) a little leaky, since I had to shove all my queries (which are pretty much business logic) into the data layer.

    So I decided to change that - made a light generic repository with some helper methods in the DAL, with code similar to:

    public class GenericRepositoryDAO : IDisposable
        {
            protected ObjectContext _ctx;
            protected bool _ownsContext;
     
            public GenericRepositoryDAO(GenericRepositoryDAO repository)
            {
                _ctx = repository._ctx;
                _ownsContext = false;
            }
    
            public GenericRepositoryDAO(DatabaseKey key = /*default*/)
            {
                _ctx = DataManager.GetContext(key); // gets an ObjectContext for a specified database
                _ctx.DefaultContainerName = key.GetDBValue();
                _ownsContext = true;
            }
     
            public void Dispose()
            {
                if (_ownsContext) _ctx.Dispose();
            }
        }
    
     public class GenericRepositoryDAO<T> : GenericRepositoryDAO where T : EntityObject, new()
        {
            public GenericRepositoryDAO(GenericRepositoryDAO repository) : base(repository) { }
            public GenericRepositoryDAO(DatabaseKey key = DatabaseKey.BusinessBook) : base(key) { }
     
            private ObjectSet<T> _objectSet = null;
            public ObjectSet<T> Entities
            {
                get
                {
                    if (_objectSet == null) _objectSet = _ctx.CreateObjectSet<T>(typeof(T).Name);
                    return _objectSet;
                }
            }
            
            public List<TResult> Query<TQueryResult, TResult> (
             Func<ObjectSet<T>, IQueryable<TQueryResult>> query, 
             Func<TQueryResult, TResult> transform, 
             bool transformNulls = false
            )
            {
                return query(Entities).ToList().Select(x => !transformNulls && x == null ? default(TResult) : transform(x)).ToList();
            }
    
            public List<TResult> GetAll<TResult>(Func<T, TResult> transform)
            {
                return Entities.ToList().Select(x => transform(x)).ToList();
            }
    
            public List<TResult> GetBy<TResult>(
             Expression<Func<T, bool>> condition, 
             Func<T, TResult> transform, 
             bool transformNulls = false
            )
            {
                return Entities.Where(condition).ToList().Select(x => (!transformNulls && x == null) ? default(TResult) : transform(x)).ToList();
            }
    
    //and similar for adding, updating, etc, etc...
        }
    
    

    Now, my business logic layer, instead of just proxying the calls, looks like this:

    public List<BusinessObjectThingy> GetInterestingBusinessObjects()
    {
        using ( var rep = new GenericRepositoryDAO<Business_object_entities>() )
        {
            return rep.GetBy(x => x.is_interesting, x => BusinessObjectThingy.CreateFromDAO(x));
        }
    }
    

    It's as close to elegant as I can get - the entities themselves don't leave the data layer, the EF context are bound to business-layer units of work via the repository, and the logic of how the query should look like (which is a simple "filter-by-property" case here, but due to the lack of control over the DB can get way out of hand in other places - thing 15-line GroupJoin() mess) lives in the business layer.

    The problem is, that now it leaks the other way - business layer needs to know about the entities, and how to query for them. Is there any smart way to keep the entities isolated in the data layer, when you can't just go Generic Repository all the way, because a generic GetAll() not returning a IQueryable will murder your database server?


    TL;DR: how do I do a generic repository while still being able to write complex LINQ queries on the Entity Framework. I guess.



  • I suppose this separation into distinct BL and DAL subprojects is enforced by the company?

    To me this kind of repository shenanigans is just cumbersome/overengineering. EF itself is your DAL. As long as you don't leak EF types from your BL I don't see any reason why the BL couldn't query EF directly.

    Maybe I'm not seeing the whole picture, and I know I'm not helping, but I personally like to keep things as simple as possible...



  • @donk said:

    EF itself is your DAL.

    If only it didn't suck at it so badly. Queries are fine, but then you try to update or insert, and it gets hairy real fast with all the context lifetime and relationship management.

    For this refactor I'm keeping a thin repository layer - basically to keep track of the context in a sane way and to shave off a couple of bytes on some more repetitive tasks, and anything more complex than a one- or two-liner gets done via a lambda:

     public List<TResult> Query<TQueryResult, TResult> (
             Func<ObjectSet<T>, IQueryable<TQueryResult>> query, 
             //...
            )
            {
                return query(Entities).ToList() //...
            }
    

    So pretty much as you say.

    That obviously means I can't swap out a repository and mock it, but given that the business logic is so heavily coupled with the underlying DB structure, it would need a miracle anyway I guess...



  • @Maciejasjmj said:

    I'm using Entity Framework

    The whole point of Entity Framework is that the data layer is created on the fly for you. Creating a formal DAL pretty much destroys all of the benefits of EF, while keeping the downsides. I would suggest you either use EF or a traditional DAL, not both. Once you switch from EF to something else (or give up the idea of a formal DAL), all of the problems you are having no longer exist.

    Also, EF is horrible for passing objects around between chunks of code because it's really hard to glue data objects to arbitrary contexts - they really want to be saved by the context that loaded them. I see you are trying to deal with that by having a singleton context, but you will run into problems if you ever pass the object to a client that isn't in the same process (like a web client via JSON).



  • @Jaime said:

    The whole point of Entity Framework is that the data layer is created on the fly for you.

    Problem is, this data layer also wants to be the view layer, the business layer, and pretty much any layer - EF works best when you use entities all the way down to binding them to your views. Which is fine when you have full control over the DB and a code-first model, less so when you're in my situation.

    So the bottom line is, the entity model is pretty fucking ugly and I'd love nothing more than to stash it into some dark corner in the codebase and forget about it.

    @Jaime said:

    Once you switch from EF to something else

    For example? Because no way I'm stringing my queries together from chunks of SQL. Apparently people here like to do that, and with disastrous results.

    Me, I like my typing like I like my coffee.

    @Jaime said:

    I see you are trying to deal with that by having a singleton context

    Quite the opposite. Those generic data repositories are not singletons - my standard BL call looks something like this actually it's likely to have a few lines more:

    public List<NiceBusinessLogicObject> GetAllInterestingBusinessObjects()
    {
        using (var boRepository = new GenericRepositoryDAO<Business_object_entity>())
        {
            return boRepository.GetBy(x => x.is_interesting, x => NiceBusinessLogicObject.FromDAO(x));
        }
    }
    

    So the context is bound to the repository's lifetime, which itself is bound to the... uh, "unit of work", I think they call it in ORM-speak. It smells a bit fishy, but I dunno.

    @Jaime said:

    Also, EF is horrible for passing objects around between chunks of code

    And that's pretty nasty, because it's a WinForms app, so you can't really have a nice request-response logic with context and object lifetime bound to request lifetime.

    Not to mention that the whole entity<->business object/view transition means I completely lose information about the context, so I need to reattach shit anyway.



  • @Maciejasjmj said:

    For example?

    You could go with one of the Micro-ORMs, like Dapper. Even the built-in TableAdapters will work, but they aren't very source control friendly - diffs are hard to read and changes are hard to merge.

    @Maciejasjmj said:

    For example? Because no way I'm stringing my queries together from chunks of SQL

    Couldn't agree more. If your organization mandates a DAL, use something that does everything you care about, and no more. Micro-ORMs fit this bill pretty well.



  • @Jaime said:

    If your organization mandates a DAL

    Well, I could probably do without, given that the other project I've browsed had SQL strings concatenated straight in the Button1_Click handlers. But I want one.

    I'd give it up if it was a nice, code-first entity model I had full control over, but it's not, I don't, it's nasty and ugly and the more I can abstract it away the better.

    @Jaime said:

    Micro-ORMs fit this bill pretty well.

    I should look into it for personal use. I doubt I'll get the organization to accept this kind of technology anytime soon - as I said, they're only now crawling out of stringly typed SQL and ad-hoc schema changes hell...

    @Jaime said:

    TableAdapters

    Hm, that's something. Will take a peek.



  • If you have "challenges" getting technology accepted, consider using T4 Templates to generate your DAL from some sort of descriptor. Maintain a JSON or XML formatted file that describes your metadata and permissible data actions, then have the template connect to the database to get the database schema and generate your methods from templates.



  • @Maciejasjmj said:

    Now, I'm using Entity Framework for my data layer.

    Well, there's your problem.

    ORM = OMG!



  • @Maciejasjmj said:

    That obviously means I can't swap out a repository and mock it, but given that the business logic is so heavily coupled with the underlying DB structure, it would need a miracle anyway I guess...

    One would assume your mock would mimick the underlying DB structure.

    That the DB structure can change out from under you at any time is a huge WTF.



  • @donk said:

    To me this kind of repository shenanigans is just cumbersome/overengineering. EF itself is your DAL.

    I agree; your structure doesn't really make sense here.

    By data layer, I thought you were asking if you should implement business logic using stored procedures. Which would have been an interesting question.

    This is just... nonsensical to me. You have an entire project which is nothing but an EDMX? What for? Weird.



  • @powerlord said:

    One would assume your mock would mimick the underlying DB structure.

    Urk. I guess that's right, but I'd feel dirty writing it.

    @powerlord said:

    That the DB structure can change out from under you at any time is a huge WTF.

    I'd rather have it change than not change, because we've managed to whip some sense into people here over the past months. Before that, it was total madness. Tables with no primary keys. Tables with no candidate keys. Tables with seven-column primary keys (have fun joining on that!). And when they do have keys, they're decimal(18,0) because "that's how we do things here".

    So, yeah. I don't mind if they change it.

    @blakeyrat said:

    I agree; your structure doesn't really make sense here.

    FUCKING HELL WHY DOES DISCOURSE PUT THE QUOTE AT THE TOP WHEN MY CURSOR IS AT THE BOTTOM... anyway.

    Well, I know it makes little sense, and I want it to make sense. That's why I ask.

    Point is, the entity model is one-to-one with the clusterfuck of the database. I can't change the database to my liking, because my application is one of numerous things that access it, and not even a particularly important one. And I'd rather not screw with custom property mapping, because I want to keep the "right click - Update Model From Database" workflow.

    @blakeyrat said:

    By data layer, I thought you were asking if you should implement business logic using stored procedures. Which would have been an interesting question.

    Not a fan of this approach, and especially with this DB, which I'm not touching with a ten foot pole.

    Besides, my BL lives closer to the UI most of the time - folks here want their grids to live-update, highlight errors and stuff, and I'd rather not roundtrip to the DB each time.

    @blakeyrat said:

    This is just... nonsensical to me. You have an entire project which is nothing but an EDMX? What for? Weird.

    EDMX and a bit of glue-code, currently. I wish I could stash everything EF-related there and work with my shiny validatable and sane business objects in the rest of the app, but the only ways to do this I know of is:

    a) implement IQueryable on them myself, and they ain't paying me enough for that
    b) eager load everything and do LINQ-To-Objects, which is out of scale even if we shrunk the database by two orders of magnitude.

    So I'm just looking for some way to hide as much of it as possible while keeping the logic in the logic layer, but failing so far.



  • @Maciejasjmj said:

    I'd rather have it change than not change, because we've managed to whip some sense into people here over the past months. Before that, it was total madness. Tables with no primary keys. Tables with no candidate keys. Tables with seven-column primary keys (have fun joining on that!). And when they do have keys, they're decimal(18,0) because "that's how we do things here".

    At Intel, our DB team in India renamed columns a couple times a week, and made everything a string. All the sprocs had to be passed tilde-delimited strings as arguments, because apparently having more than two arguments is hard?



  • I'll admit that I'm not familiar with Entity Framework, but in Java's JPA any time the DB types change, my classes have to change to match them. i.e. changing from decimal to long for the primary key.

    The documentation I've seen for EF implies that this same rule would be applicable there.



  • @powerlord said:

    any time the DB types change, my classes have to change to match them

    Yeah. EF has it automated though - you just tell the designer to update your classes from the database and it does so (probably breaking a nice chunk of your code).



  • @Maciejasjmj said:

    I want to keep the "right click - Update Model From Database" workflow.

    As somebody suggested above you could edit the T4 template. The EDMX bases the class structures off of this file (though the actual final content is based upon the database).



  • @Maciejasjmj said:

    Point is, the entity model is one-to-one with the clusterfuck of the database. I can't change the database to my liking, because my application is one of numerous things that access it, and not even a particularly important one. And I'd rather not screw with custom property mapping, because I want to keep the "right click - Update Model From Database" workflow.

    I'd put all the data access/data models/business rules in the same project, and segregate-out the view models. The view models are really the freak here.

    @powerlord said:

    but in Java's JPA any time the DB types change, my classes have to change to match them. i.e. changing from decimal to long for the primary key.

    The documentation I've seen for EF implies that this same rule would be applicable there.

    EF's the same way, but since the POCO classes are all auto-generated, you can just have EF re-generate them. You might get build errors, but at least C# is strongly-typed enough that they're usually pretty easy to find and fix.

    @rad131304 said:

    As somebody suggested above you could edit the T4 template. The EDMX bases the class structures off of this file (though the actual final content is based upon the database).

    EF (at least EF6) generates partial classes, so you can just make a new partial and add whatever the heck you want to the auto-generated POCOs.



  • @blakeyrat said:

    EF's the same way, but since the POCO classes are all auto-generated, you can just have EF re-generate them. You might get build errors, but at least C# is strongly-typed enough that they're usually pretty easy to find and fix.

    I'm guessing that's only true if you do Database First as opposed to Code First.



  • @powerlord said:

    I'm guessing that's only true if you do Database First as opposed to Code First.

    If you did Code First, you'd be able to make your POCOs into partial classes yourself... right?

    I've never done Code First.



  • @blakeyrat said:

    @rad131304 said:
    As somebody suggested above you could edit the T4 template. The EDMX bases the class structures off of this file (though the actual final content is based upon the database).

    EF (at least EF6) generates partial classes, so you can just make a new partial and add whatever the heck you want to the auto-generated POCOs.

    True, that's a good route for a lot of stuff if it's supported; I was thinking more of doing things like changing the collections from DbSet to IDbSet. Depending on what he is looking to do, it might make more sense to modify the templates.



  • If you're going to use your own templates, I would suggest starting with something lighter than EF. Grab PetaPoco, keep their core template and replace the generator template with one that does exactly what you want. We use this to make a set of partial classes that take care of the repetitive work in our business layer



  • @Jaime said:

    If you're going to use your own templates, I would suggest starting with something lighter than EF. Grab PetaPoco, keep their core template and replace the generator template with one that does exactly what you want. We use this to make a set of partial classes that take care of the repetitive work in our business layer

    I'd need to learn more about T4 first - the modifications to the template we did were only so we could mock enough of DbContext with an in memory object during testing to be confident about stuff working properly - basically swapping concrete classes with interfaces. I think we modified 2 or 3 lines in total.


  • Discourse touched me in a no-no place

    @blakeyrat said:

    I've never done Code First.

    It's fine if the DB is subservient to the application (e.g., it's just acting as a persistent data store) and saves a bunch of typing. If it's the other way round, which happens any time the DB is shared between apps, then You Don't Want Code First.

    @powerlord said:

    in Java's JPA any time the DB types change, my classes have to change to match them. i.e. changing from decimal to long for the primary key.

    I think you can specify a type mapping explicitly if you prefer. It's ages since I looked into this.



  • @dkf said:

    It's fine if the DB is subservient to the application (e.g., it's just acting as a persistent data store) and saves a bunch of typing. If it's the other way round, which happens any time the DB is shared between apps, then You Don't Want Code First.

    How does code first save a bunch of typing?

    I'd argue that a code-first model actually is easier to maintain as the database grows (i.e. is of non-trivial size). The usability of the visual edmx editor leaves a lot to be desired (not to mention editing the raw edmx file itself 😕), whereas you have complete control if you write your own classes and specify your own mapping.

    Though it's often a headache to create a good code-first model from an existing database (again, of non-trivial size), even with the available tooling, we've been ditching the edmx file and converted to code first in a couple of bigger projects since it (at least for us) will pay off in the long run. (And since only code first is supported in EF7...)

    IMO, YMMV etc. And both approaches suck in their own respective ways.


  • Discourse touched me in a no-no place

    @donk said:

    create a good code-first model from an existing database

    :facepalm:



  • There is, thankfully, tooling for that. Admittedly ass-backwards approach (not so much "code first" anymore eh), but whatcha gonna do?

    (How about not using EF in the first place? Yeah, there's that...)



  • Your approach looks like some variant of the Unit of Work pattern.

    A colleague did something like this in a WebForms app. It always felt like an overkill and is super awkward to work with.

    My most current workflow was to do the same thing you used to have. Create a DAL project, that just has the DB-first edmx models. Then add a BL project that creates a sematic facade on top of the EF models. Your POCO business models go into the methods named like "UserManager.FindUserByName(string name)". POCO business models come out. EF is contained inside BL.

    But then, you have the problem of your BL being tied to the icky EF models, inability to test etc. Which is why you were hoping for a better solution.

    Sigh. I read through this thread hoping to find some better idea too. But alas, seems no one has any.


  • FoxDev

    @cartman82 said:

    Then add a BL project that creates a sematic facade on top of the EF models.

    done right you can totes do this and make something that is testable and maintainable.

    it won't be fun to write, but that's the tradeoff i guess.

    Basically you create the DAL abstraction around EF in such a way that you can mock out the entire thing, then in the tests you give the BL the mocked version and in prod you give the BL the real DAL. Of course you have to test the DAL, but that's where in memory databases come in. EF is capable of connecting to those given the proper connection string.

    it won't be fun, but it is doable.



  • @accalia said:

    Basically you create the DAL abstraction around EF in such a way that you can mock out the entire thing, then in the tests you give the BL the mocked version and in prod you give the BL the real DAL. Of course you have to test the DAL, but that's where in memory databases come in. EF is capable of connecting to those given the proper connection string.

    it won't be fun, but it is doable.

    "Creating a DAL abstraction" sounds like a whole additional layer. Meaning, for each model, write an Interface-derived facade to the corresponding EF class. But if you go that route, you might as well go code-first.

    In anything but an absolutely critical project, I'd probably go "screw that" and just expose the EF autogenerated crap directly to the BL layer.



  • @accalia said:

    Basically you create the DAL abstraction around EF

    Yeah, but that's the actual problem - how to create such abstraction.

    I mean, I need to query the DB, so I need to be aware of the EF model. I can't just have a generic repository with GetAll<>() and GetByID<>(), because the queries can get complex and they need to execute against the database, not have the whole gajillion-record sets eagerly loaded. And doing specialized repositories in the DAL just means I'm shifting the BL to the DAL (that was the first approach, actually - but that ended up making very little sense, as all the cogs and wheels were in the DAL, and the BL only had the proxy methods calling back to the DAL).


  • FoxDev

    @cartman82 said:

    "Creating a DAL abstraction" sounds like a whole additional layer.

    it is.

    like i said, not fun

    you could go halfway and just use the EF objects against an in memory database that you prefill with your test data if the extra layer is too much, but then you have integration tests and you have to keep your seed data for the in memory DB up to date with schema changes or all the tests break rather than just the DAL tests.


  • FoxDev

    @Maciejasjmj said:

    because the queries can get complex and they need to execute against the database, not have the whole gajillion-record sets eagerly loaded.

    oh. that situation.

    .... May i recommend shifting that business logic into stored procedures then? you'll want to spend some time making sure you only put the truely troublesome queries in as stored procedures as they cause a fair bit of headache, but that's really the only way i've found of handling the "sort through gigabytes of data for the couple of KB i'm interested in across like ten tables" situration.



  • @accalia said:

    oh. that situation.

    Well, yeah. I mean, if I could just do GetAll on the database, I would hide it behind a generic repository.

    @accalia said:

    .... May i recommend shifting that business logic into stored procedures then?

    Huh... how does that improve unit testability? Also, as I said before, a) the database is a pile of mess I want to have less, not more to do with, and b) it's a WinForms, active app, and I'd rather not roundtrip every time an user farts the wrong way at the application.


  • FoxDev

    @Maciejasjmj said:

    Huh... how does that improve unit testability?

    it doesn't really. you'll need to mock the data layer to unit test business logic.

    the stored procedure moves the BL into the database entirely, making it impossible to unit test, but there is benefit for that cost. for the cost of being untestable you get a significant performance improvement over performing the business logic outside of the database.

    @Maciejasjmj said:

    the database is a pile of mess I want to have less, not more to do with

    ah....

    I agree there....

    @Maciejasjmj said:

    it's a WinForms, active app, and I'd rather not roundtrip every time an user farts the wrong way at the application.
    with proper caching you can avoid a lot of round trips to the server, but even so you'll need a fair few round trips no matter what.

    This is a nasty situation you've found yourself in, i see no clear path out of it, but you are not without options.


  • I survived the hour long Uno hand

    @accalia said:

    you'll need to mock the data layer to unit test business logic.

    the stored procedure moves the BL into the database entirely, making it impossible to unit test,

    Not strictly true. There are tools that will let you mock the tables so you can unit test the stored procs. It's just harder to do than testing it in the code.

    I prefer to do the data logic (assembling information from disparate tables and views into a coherent set of rows) in the procs and the business logic in the code. So I'd say complex queries go in the data layer. But I don't do a lot of .net programming so I'm not sure what the norms are there.


  • FoxDev

    Correction accepted, and broadly speaking i agree with you on the role of sprocs, however there are times where preformance overwhelms best practices and pushing BL into the data layer is necessary (3m salesorder records and counting.... some queries used to take minutes before i pushed the BL intot he DB, now the same queries take tens of ms)



  • @Yamikuronue said:

    I prefer to do the data logic (assembling information from disparate tables and views into a coherent set of rows) in the procs and the business logic in the code.

    That really seems easier said than done. A simple join-select-filter on relatively constant values is easy to stuff in a view, but beyond that the line between data logic and business logic blurs fast. Is, for example "column X should be taken from table Y if date D is Friday, otherwise from table Z" a piece of data logic (because it's just a join), or business logic (because that Friday can become Saturday on the whim)?


  • I survived the hour long Uno hand

    I accept your point, but I'd put that in data logic, because the reason it's in another table is a serialization detail rather than a business detail.

    If there's a semantic meaning behind the alternate friday thing, I'd probably set up a flag and do the date bit in the calling code.



  • @Yamikuronue said:

    because the reason it's in another table is a serialization detail rather than a business detail

    Depends. If the tables represent some business entities, it might well be a business detail (cf. "amount due should be taken from the weekly summaries if the entry date is Friday, otherwise it should be a sum of daily summaries for the week")

    @Yamikuronue said:

    If there's a semantic meaning behind the alternate friday thing, I'd probably set up a flag and do the date bit in the calling code.

    The problem is, if you go back-and-forth between the business code and the data code too much, you pretty much inevitably end up having to materialize more data than you need. EF is nice enough to let you build the query StringBuilder-style, but even then occasionally you hit a roadblock of

    return entities.Where(x => SomeDotNetFunction(x)); //and EF complains that it can't translate it to SQL
    

    And in the DB it's even worse, because you can't really split the logic in two: workflow like

    var entries = database.GetABillionEntries();
    if (BusinessLogic.CrunchSomeNumbers(entries) == 5) return entries.First();
    return entries.Last();
    

    makes baby Jesus cry, but you'll keep ending up with things like this all the time if you keep the business and data logic separate.


  • Discourse touched me in a no-no place

    @Maciejasjmj said:

    workflow like
    [...]
    makes baby Jesus cry

    That is a good instinct. If what you're doing causes The Light Of The World to need a kleenex, you might need to reconsider…


Log in to reply