Object Orientated Linq2Sql query class

Question

For my internship I'm creating a program that communicates with a database on the background. The program is layered in the MVC(Model-View-Controller) way.

For the View I want to access the data through something I called the DataAccesLayer(DAL). Because that view has minimal knowledge I want it to pass an ID for the query I want to call. The calling will be done inside the DAL. Then with the ID I want to ask a class that holds the queries to return the query to then execute it inside the DAL. Picture for visualizing.

enter image description here

The problem I have now is how to execute the query in my Read function. The code for DAL is the following:

public class DataAccesLayer
{
    private Queries queryloader;
    private RoadsoftDigitacV8DataContext db;

    public DataAccesLayer()
    {
        queryloader = new Queries();
        db = new RoadsoftDigitacV8DataContext();
    }

    public List Read(int ID)
    {
        IQueryable query;
        query = queryloader.GetQuery(ID);

        return query.ToList();
        
    }

}

The Code for the Queries class:

public class Queries
{
    private Dictionary<int, IQueryable object> queryDict;
    private ErrorLoggerWinLog logger;

    public Queries()
    {
        logger = ErrorLoggerWinLog.Instance();
        queryDict = new Dictionary<int, IQueryable object>();
        queryDict.Add(1, from d in db.Drivers
                         select d);
    }

    public object GetQuery(int ID)
    {
        var query;
        if(queryDict.TryGetValue(ID, out query) == false)
        {
            logger.WriteLine("Queries", "Could not find the query specified", ErrorLoggerWinLog.loggerlevel.Error);
        }
        return query;
    }
}

I'm wondering, is this possible? Right now it doesn't seem to work. I'm probably forgetting something or missing something important. Does anyone have any experience with this kind of set-up, or should be looking at a totally different solution?

Edit: Right now it doesn't seem to execute the query, like I'm missing a command in the read function. The Datacontext is filled though, that's being done in a different section of the program.

Edit2: Right now I'm looking into the IRepository Pattern, it's a great learning experience, Thank you all who took time to comment and anwser!

You would still need to connect the db context object to the actual database. As you have it now, you have an unconnected context. Then you would need to actually execute the query through the context. — ThaMe90, Aug 29 '13 at 09:20
Why do you need this "Query like" solution? I'd create a method called GetDriveres on the DataAccesLayer and call this directly from the Controller(and your View class should actually be a Controller, since the View is for visualizing the data) — laszlokiss88, Aug 29 '13 at 09:25
laszlokiss88, that be great if that would be the only query that would be needed, but as for now I already compiled a list of 30 queries and counting. That's just the select queries. — Freakz0rz, Aug 29 '13 at 09:38
@Freakz0rz this is what the DataAccessLayers for. However, if you have a lots of similar queries like GetDrivers, GetCars, etc without filtering you can create a generic IQuerable Get() method. Not to mention, what will you do in the Controller? You will cast the object result of the current Read method? This isn't convenient either... — laszlokiss88, Aug 29 '13 at 09:44

score 3 · Answer 1 · edited May 23 '17 at 12:30

At any time your queryDict has only one element; the only time that GetQuery() doesn't log an error message and doesn't return null is when you pass 1 to it. as a consequence Read(1) returns a list of all Drivers, otherwise throws a NullReferenceException because, well, query is null.

You should use something like this:

    public class DriversDAL
    {
        private RoadsoftDigitacV8DataContext db;

        public DriversDAL()
        {
            db = new RoadsoftDigitacV8DataContext();
        }

        public Driver GetDriver(int ID)
        {
            return db.Drives.Single(d => d.ID == ID);
        }

    }

If you want a generic solution, you should use a generic dao and the Repository pattern:

How To Create Generic Data Access Object (DAO) CRUD Methods with LINQ to SQL

Object Orientated Linq2Sql query class

1 Answers1