CODEWITHSUNDEEP
c#sql-serverentity-framework
RezaNoei
RezaNoei

Reputation: 1479

Creating and injecting SQL query into DAL

I want to write a DAL to do mapping on input model, data normalization and some other encryption tasks at low-levels of my app, and i want to limit my SQL queries to run only through DAL. i changed my dbContext to private to achieve last one. but in order to pass queries to DAL, i need to a way of building and passing all types of SQL queries that i could inject them into DAL and then run final query against main EntityModels.

for example i tried this way without any result:

Main.cs:

IEnumerable<DataAccess.Model.Group> Output = new List<DataAccess.Model.Group>();
Output = from A in Output.Where(P => P.Name.StartsWith("A")) select A;
Group.FakeSelect(Output);

and in my DAL i tried something like it:

public List<Group> FakeSelect(IEnumerable<Group> Query, out List<NewGroup> NewModel)
{
    IQueryable<Group> Source = GetQuery();
    Query = Query.Union(Source);
    return (from A in Query select new NewGroup{....}).ToList();
}

Im using this function to get main entityModel query :

public IQueryable<DataAccess.Model.Group> GetQuery()
{
        ShamsEntities Entities = new ShamsEntities();
        return Entities.Set<DataAccess.Model.Group>();
}

I expected to see all rows with Names that startwith("A") but the result was whole table's rows.

Upvotes: 0

Views: 1351

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

Writing queries is a business logic concern, not a DAL concern. Which entities are needed, how they are shaped and sorted are details of a business logic method, or transaction script. If you are in the middle of writing a bit of business logic and need to jump over to your DAL to implement a new method that returns the data you need, you are mixing concerns.

The DAL should hide the details of the repository used, and so LINQ was invented so the queries could be expressed in a repository-agnostic way, and in the language of the business logic layer.

LINQ also enables query composition and deferred execution, and so the intended, and recommended way to interact with a LINQ-enabled repository or DAL (like an EF DbContext) is for the business logic layer to start with an IQueryable property, and build up a custom query.

Upvotes: -2

Milney
Milney

Reputation: 6417

If you pass SQL queries in to your DAL from another layer - then it isn't your DAL, as SQL is inherantly "DA (Data Access)" and so should only live in the DAL. You probably want to either create a method for each type of query, pass a Filter type object, or maybe an expression object to your DAL, which can then parse and run the relevant SQL. Otherwise, if you want to swap out your Data Access method, for example using XML files instead of a database - you will have to change all your code, not just your DAL, defeating the point in seperating it out into a different layer in the first place!

P.S. Encryption probably shouldn't be the responsibility of the DAL...

Edit: This paragraph from the article you linked explains what i mean:

Encapsulate data access functionality within the data access layer. The data access layer should hide the details of data source access. It should be responsible for managing connections, generating queries, and mapping application entities to data source structures. Consumers of the data access layer interact through abstract interfaces using application entities such as custom objects, TypedDataSets, and XML, and should have no knowledge of the internal details of the data access layer. Separating concerns in this way assists in application development and maintenance.

Upvotes: 3

Related Questions