Pass in an Expression to linq's Select

Question

This is linq-to-sql

I have a lot of different classes all doing the same query, but projecting the results slightly differently. Ideally I'd like to be able to have the query in one place, and have the projection passed into the Select method. It works fine for concrete types:

public void GetResults() {
    var junk = db.SiteProducts.Select(Project());
}

public Expression<Func<DbEntities.SiteProduct, string>> Project() {
    return p => p.ProductAlert;
}

But when I try to return an anonymous type, it fails

public void GetResults() {
    var junk = db.SiteProducts.Select(Project());
}

public Expression<Func<DbEntities.SiteProduct, TResult>> Project<TResult>() {
    return p => new { p.ProductAlert };
}

I fully understand why generic type inference is failing in the second case. But is there a trick—short of crafting my own Expressions from the ground up—I'm missing that could get this to work?

Answer 1

This is an intriguing question. I think a DTO can help you out here, but there are limitations and pitfalls to watch out for. Take the following LINQPad Example:

class ProjectDTO
{
    public string Name { get; set; }

    public static Expression<Func<Project, ProjectDTO>> ToDTO = (e) => new ProjectDTO
    {
        Name = e.Name
    };

    public ProjectDTO() {}

    public ProjectDTO(Project project)
    {
        Name = project.Name;
    }
}

void Main()
{
    Projects.Select(p => p.Name).Dump();
    Projects.Select(ProjectDTO.ToDTO).Dump();
    Projects.Select(p => new ProjectDTO(p)).Dump();
}

SQL Generated:

SELECT [t0].[Name]
FROM [Project] AS [t0]
GO

SELECT [t0].[Name]
FROM [Project] AS [t0]
GO

SELECT [t0].[ProjectId], [t0].[Name], [t0].[Description], [t0].[DateCreated], [t0].[DateModified], [t0].[DateComplete], [t0].[CreatedBy]
FROM [Project] AS [t0]

As you can see, you cannot use a copy-constructor to assign the properties of the DTO as this forces the entire object to be pulled back from the database.

This also slightly limiting if you wanted to extend the base DTO and add more properties for more specialised views of the data, which means you could end up with multiple Expression's with similar code.

However, I quite like option two, but i'm sure this option is quite likely restricted to single type projections, consider the following example:

var query = from p in Projects
            join t in Tasks on p.ProjectId equals t.ProjectId
            select ProjectDTO.ToDTO; //Can't be used like this

I don't think you can use the Expression in this type of query-syntax. Generally speaking, I don't think there will be a solution that works across the board. You may have to review your design to see if you can provide less projections, based on some of the properties being very cheap to always include in the query?

Without using the Dynamic LINQ library or building the expression tree manually, I would also like to see if it is possible with LINQ-SQL/LINQ-Entities to create dynamic selects.

Answer 2

If I understand your question correctly you can use this code:

first declare a method for selecting your data like this:

public List<TResult> FindAll<TResult>(Func<Regions, TResult> selector) where TResult : class
    {
        using (RepositoryDataContext = new DataClasses1DataContext())
        {
                return RepositoryDataContext.Regions.Select<Regions, TResult>(selector).ToList<TResult>();

        }
    }

then you can build your select statement like this:

Func<Regions, SelectAllRegion> select = r => new SelectAllRegion
        {
            RegionID = r.RegionID,
            RegionDescription = r.RegionDescription
        };

my SelectAllRegion :

 public class SelectAllRegion
{
    public SelectAllRegion()
    {
    }
    public int RegionID { get; set; }
    public string RegionDescription { get; set; }
}

and region is Region table in northwing.I hope this help you