CODEWITHSUNDEEP
c#entity-frameworklinq
Illidan
Illidan

Reputation: 4237

Avoiding duplicate code in Linq Select method

I have some stupid situation where I can't find simple and elegant solution for annoying problem.

I writing asp.net application with simple UI for querying database based on user choices. For querying DB I using Entity Framework.

In my scenario, one of the optional user choices is a free text, and for that I using SQL Server FTS capabilities (using CONTAINSTABLE function, since I need to order results by relevance).

Now, I have the following situation:

If user did not provided free text criteria, I creating simple Linq query with result of type IQueriable<Result> (where 'Result' is an entity with data from table)

But, if user did provided free text criteria, I creating query with CONTAINSTABLE, and its type is IQueriable<ResultWithRank> (where 'ResultWithRank' is a object contains two entities: 'Result' and 'Rank', since CONTAINSTABLE results in inner join).

At this stage, after I built my queriable, I need to execute 'Select' method on it, in order to convert it to something useful.

Here is the problem:

In first case, my select statement looks like this:

var result = queryable.Select(entity => /*.. about 20 lines of reshapying code .. */

In second case, it looks like this:

var result = queryable.Select(entity.Result => /*.. about 20 lines of exactly the same reshapying code .. */

I want to avoid duplication of "select" code, because it identical for both cases. I tried to move it to external method, but this was failing to execute, since Linq was trying to convert my method to SQL and obviously failed.

How I can resolve this problem in some elegant way, without duplicating 'select' code?

Upvotes: 3

Views: 1101

Answers (2)

Ogglas
Ogglas

Reputation: 69968

@JonSkeet is correct but I struggled a lot with getting everything to work. The key is to use AsQueryable() before the Select is made. You can of course get it to work without it but there is a high probability that you will fetch more columns than you actually need.

Example:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

using var context = new MyDbContext();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

var blogPostIds = context.Blogs
    .Select(b => new
    {
        BlogId = b.Id,
        PostIds = b.Posts.AsQueryable().Select(Helper.Selector).ToList()
    })
    .ToList();

public static class Helper
{
    public static Expression<Func<Post, int>> Selector
        => x => x.Id;
}

public class MyDbContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=Selector;Integrated Security=SSPI;")
            .LogTo(Console.WriteLine, LogLevel.Information);
}

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
    public IEnumerable<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Source: https://stackoverflow.com/a/76047514/3850405

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500245

I'd use:

Expression<Func<Result, Foo>> conversion = result => { ... };

First case:

var result = queryable.Select(conversion);

Second case:

var result = queryable.Select(entity => entity.Result)
                      .Select(conversion);

Basically use the fact that you can perform two projections together, so that your first projection (in the second case) gets you into a situation you've already tackled.

Upvotes: 8

Related Questions