CODEWITHSUNDEEP
c#entity-framework-6
RezaNoei
RezaNoei

Reputation: 1479

apply where clause and column limitations on EF queries before sending them to SQL Server

I'm beginner in EF 6.0 and I've started to design an easy to use DAL. Here is my interface for all operational task on each entities : 

public interface IEntity<T>
{
    T Insert(T NewItem);
    T Update(T ModifiedItem);
    bool Delete(T Selected);
    bool DeleteByID(int ItemID);
    List<T> Select(Func<T, bool> Query);
    IQueryable<T> GetQuery();
}

here is my implementation for Select method on 'Group' Entity:

public bool Select(Func<DataAccess.Model.Group, bool> Query, out List<Business.Entity.Login.LoginUser> Output)
{
    IQueryable<DataAccess.Model.Group> Connection = GetQuery();
    Output = Connection.Where(Query).ToLoginUser().ToList();
    return true;
}

I will receive all range limiters on fields using Func<,> parameter and I'm using below Extension Method to hide some of my not needed columns: 

    public static IEnumerable<BEntities.Login.LoginUser> ToLoginUser(this IEnumerable<DataAccess.Model.Group> Model)
    {
        return from A in Model select new Business.Entity.Login.LoginUser { Username = A.Name, Password = A.Identifier };
    }

but what will actually runs on SQL Server (in diagnostic window) is :

SELECT
[Extent1].[ID] AS[ID],
[Extent1].[Identifier] AS [Identifier],
[Extent1].[Name] AS [Name],
[Extent1].[ParentGroup] AS [ParentGroup],
[Extent1].[GroupPath] AS [GroupPath]
FROM[dbo].[Group] AS [Extent1]

I cant see any 'Where' clause or any column limitation on this query. where is my mistake ?

Upvotes: 1

Views: 41

Answers (1)

RezaNoei
RezaNoei

Reputation: 1479

using Expression<...,...> changed my generated SQL Query to something like it:

[Extent1].[ID] AS [ID], 
[Extent1].[Identifier] AS [Identifier], 
[Extent1].[Name] AS [Name], 
[Extent1].[ParentGroup] AS [ParentGroup], 
[Extent1].[GroupPath] AS [GroupPath]
FROM [dbo].[Group] AS [Extent1]
WHERE N'Noei' = [Extent1].[Name]

but i also used an model mapper to limit some of it's columns:

public static IEnumerable<BEntities.Login.LoginUser> ToLoginUser(this IEnumerable<DataAccess.Model.Group> Model) 
{
    return from A in Model select new Business.Entity.Login.LoginUser { Username = A.Name, Password = A.Identifier };
}

and i called it in this way:

public bool Select(Expression<Func<DataAccess.Model.Group, bool>> Query, out List<Business.Entity.Login.LoginUser> Output)
{
    IQueryable<DataAccess.Model.Group> Connection = GetQuery();
    Output = Connection.Where(Query).ToLoginUser().ToList();
    return true;
}

but i expected to see column hidings in generated Query.

Upvotes: 1

Related Questions