6

I have some queries that I use all the time (like get rows only of some specific type, or count something etc.). I'd like to organize them somehow (better then I currently do).

Currently I have them all in a static class called Queries which I don't really like because it's like a I-don't-know-how-to-name-this-class.

Is there a preferred way for organizing predefined queries in Entity Framework by extending some types etc.?

As requested here is an example showing how I use it:

All of my queries:

public static class Queries
{
    public static IEnumerable<Light> GetEnabledLights()
    {
        using(var context = new MyEntities())
        {
             return context.Lights.Where(l => l.Enabled);
        }
    }
}

Code using some query:

public static class LightsLoader
{
    public static void LoadLights()
    {
        var enabledLights = Queries.GetEnabledLights().ToList();
        if (enabledLights.Count == 0)
        {
            throw new InvalidOperationException("There are no enabled lights.");
        }
        // ... more code
    }
}

Faking the query and testing the business logic:

[TestClass]
public class LightsLoaderTests
{
    [TestMethod]
    [ExpectedException(typeof(InvalidOperationException))]
    public void TestNoEnableLights()
    {
        using(ShimContext.Create())
        {
            ShimQueries.GetEnabledLights = () => Enumerable.Empty<Light>();
            LightsLoader.LoadLights();
        }
    }
}

But back to the question: I just thought Entity Framework might offer a better way for organizing queries then putting them all in a God-Class ;-)

Improve this question
8
  • Are you making full use of Linq? Commented Mar 3, 2015 at 16:34
  • Of course. Without using linq I see no value in using entity framework ;-) Commented Mar 3, 2015 at 20:05
  • Then you already have several tools at your disposal for dealing with this kind of thing. Ad-hoc queries on a collection using Linq don't have to be sequestered in methods; you can write them inline. Commented Mar 3, 2015 at 20:12
  • 1
    The reason why I move them somewhere else is because I want to be able to mock the query for unit tests. If I had them inline it wouldn't be possible. Commented Mar 3, 2015 at 20:23
  • 1
    I don't test entity framework (it would be pointless, you're right) but the business layer that digests the data comming from the database so I need to fake the query results to simulate possible cases. Commented Mar 3, 2015 at 20:26

2 Answers 2

Reset to default
3

Extending the ORM Context is a good approach, but in a system with lots of predefined queries, I use to take another approach, using Extensions methods in order to group the related queries. Let me give an example:

The usage is kept simple like this:

var data = Context.Financial() // This returns a FinancialContextExtensions instance with all Financial grouped queries
                  .GetComplexReportData() // This returns the actual result for your query
                  .ToArray();

I create a file FinancialContextExtensions:

public static partial class ContextQueryExtensions
{
    public static FinancialContextExtensions Financial(this DataContext context)
    {
        return new FinancialContextExtensions(context);
    }
}

public class FinancialContextExtensions : ContextExtensions
{
    protected readonly DataContext Context;

    public FinancialContextExtensions(DataContext context)
    {
        this.Context = context;
    }

    public IEnumerable<FinancialReportInfo> GetComplexReportData(DateTime dateBeing, DateTime dateEnd)
    {
        return from a in Context.AAA
               join b in Context.BBB on ...
               where ...
               group ...
               select new FinancialReportInfo { ... };
    }
}

PS: I don't know if this pattern has a name, but a I remember reading it on a site, don't know what site though...

Improve this answer
5
  • This is a really nice approach. I think I already know where I can use it my project. I like the example. One more question. Why is the ContextQueryExtensions partial? What is in the other part? Commented Mar 3, 2015 at 21:14
  • I can have a MarketdataContextExtensions class, that I would create in it's own file with the corresponding partial class for ContextQueryExtension on top of it. But you can create one file for the ContextQueryExtensions alone, and place all the Extension methods there. Commented Mar 3, 2015 at 21:18
  • I am against using extension methods for extending code that you control, unless it is an interface. Extension methods end up being put in separate code files and can hurt discoverability in larger projects. That being said, there isn't really anything wrong with this approach. Commented Mar 3, 2015 at 21:38
  • @mortalapeman If you prefer you can have the code that creates the query agregator directly in your DataContext Commented Mar 3, 2015 at 21:40
  • @RMalke Oh I know, I just like express my opinion on things of little importance xD. At this point it is all just personal preference. Commented Mar 3, 2015 at 23:44
1

Sounds like you are organizing your code by what the code is and not the domain it applies to. As a developer working with the entity framework, I would expect you to have a derived DataContext class with all your general CRUD operations. Since this DataContext is your gateway to the database, it would make sense to store the queries and methods that use them in your derived class. I suggest not exposing the literal query strings publicly, keep them private and create public methods that use them internally.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.