Avoiding duplicate code in Linq Select method

Question

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?

Just a guess: are .Select(x => ..) and .Select(x => x.Result).Select(x => ...) an option? — Loetn
– Loetn, Commented Jan 7, 2016 at 7:55

Hamlet Hakobyan · Accepted Answer · 2016-01-07 08:08:27Z

8

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.

edited Jan 7, 2016 at 8:08

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

answered Jan 7, 2016 at 7:56

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hamlet Hakobyan Over a year ago

Why not var result = queryable.Select(entity => conversion(entity.Result));? Isn't there difference in performance perspective?

Jon Skeet Over a year ago

@HamletHakobyan: That wouldn't compile... conversion is just an expression tree, which isn't invokable. You don't want to compile it into a delegate, as at that point EF can't convert it to SQL.

Illidan Over a year ago

@Jon: I was trying to make select after select, and this is what I get "The nested query does not have the appropriate key". Any ideas?

Jon Skeet Over a year ago

@Illidan: Nope, I'm afraid I've never seen that.

Ogglas · Accepted Answer · 2023-05-12 14:16:39Z

@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

Collectives™ on Stack Overflow

Avoiding duplicate code in Linq Select method

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related