Expression> with Linq Select - CS1929 List does not contain a definition for 'Select' and the best extension method

Question

I get the following error that I'm having trouble solving:

CS1929
'List<ExternalLink>' does not contain a definition for 'Select' and 
the best extension method overload 
'Queryable.Select<ExternalLink, ExternalLinkDto>(IQueryable<ExternalLink>,
    Expression<Func<ExternalLink, ExternalLinkDto>>)' 
requires a receiver of type
'System.Linq.IQueryable<MyProject.Models.Entities.ExternalLink>'

What I'm trying to achieve is this example from @JonSkeet

https://stackoverflow.com/a/34649951/3850405

The reason for doing this is exactly as the original post, avoid duplicate code for LINQ Select and still only project the properties I need for querying efficiently via EF Core.

https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying#project-only-properties-you-need

public static IQueryable<ProductSummaryDtoHelper> QueryProductSummary(this IQueryable<Product> query)
{
    var summary = query.Select(product => new ProductSummaryDtoHelper()
    {
        UnresolvedThreatAndCountermeasures =
            product.ThreatAndCountermeasures.Where(tac => !tac.NotApplicable && !(tac.Verified && tac.Implemented)).Select(
                vuln => new ProductSummaryVulnerabilityDtoHelper()
                {
                    ExternalLinksHelper = vuln.ExternalLinks.Select(ExternalLinkDto.Conversion),
                    ExternalLinksHelperThreat = vuln.Threat.ExternalLinks.Select(externalLink => new ExternalLinkDto()
                    {
                        Id = externalLink.Id,
                        Link = externalLink.Link,
                        LinkType = externalLink.LinkType,
                        LinkText = externalLink.LinkText
                    }),
                    ExternalLinksHelperThreatFork = vuln.Threat.Fork.ExternalLinks.Select(externalLink => new ExternalLinkDto()
                    {
                        Id = externalLink.Id,
                        Link = externalLink.Link,
                        LinkType = externalLink.LinkType,
                        LinkText = externalLink.LinkText
                    })
                })
    });

    return summary;
}

ExternalLinkDto class:

public class ExternalLinkDto : EntityDto
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    ...

    public static Expression<Func<ExternalLink, ExternalLinkDto>> Conversion = externalLink =>
            new ExternalLinkDto()
            {
                Id = externalLink.Id,
                Link = externalLink.Link,
                LinkType = externalLink.LinkType,
                LinkText = externalLink.LinkText
            };
}

The following code works but I do not like the code duplication:

public static IQueryable<ProductSummaryDtoHelper> QueryProductSummary(this IQueryable<Product> query)
{
    var summary = query.Select(product => new ProductSummaryDtoHelper()
    {
        UnresolvedThreatAndCountermeasures =
            product.ThreatAndCountermeasures.Where(tac => !tac.NotApplicable && !(tac.Verified && tac.Implemented)).Select(
                vuln => new ProductSummaryVulnerabilityDtoHelper()
                {
                    ExternalLinksHelper = vuln.ExternalLinks.Select(externalLink => new ExternalLinkDto()
                    {
                        Id = externalLink.Id,
                        Link = externalLink.Link,
                        LinkType = externalLink.LinkType,
                        LinkText = externalLink.LinkText
                    }),
                    ExternalLinksHelperThreat = vuln.Threat.ExternalLinks.Select(externalLink => new ExternalLinkDto()
                    {
                        Id = externalLink.Id,
                        Link = externalLink.Link,
                        LinkType = externalLink.LinkType,
                        LinkText = externalLink.LinkText
                    }),
                    ExternalLinksHelperThreatFork = vuln.Threat.Fork.ExternalLinks.Select(externalLink => new ExternalLinkDto()
                    {
                        Id = externalLink.Id,
                        Link = externalLink.Link,
                        LinkType = externalLink.LinkType,
                        LinkText = externalLink.LinkText
                    })
                })
    });

    return summary;
}

This results in the following SQL sent:

SELECT [t0].[Id], [t1].[Id] AS [Id0], [t3].[Id] AS [Id1], [e].[Id] AS [Id2], [e].[Link], [e].[LinkType], [e].[LinkText], [e0].[Id] AS [Id3], [e0].[Link] AS [Link0], [e0].[LinkType] AS [LinkType0], [e0].[LinkText] AS [LinkText0], [e1].[Id] AS [Id4], [e1].[Link] AS [Link1], [e1].[LinkType] AS [LinkType1], [e1].[LinkText] AS [LinkText1], [t0].[ProductId]
  FROM [ThreatAndCountermeasure] AS [t0]
  INNER JOIN [Threats] AS [t1] ON [t0].[ThreatId] = [t1].[Id]
  LEFT JOIN [Threats] AS [t3] ON [t1].[ForkId] = [t3].[Id]
  LEFT JOIN [ExternalLink] AS [e] ON [t0].[Id] = [e].[ThreatAndCountermeasureId]
  LEFT JOIN [ExternalLink] AS [e0] ON [t1].[Id] = [e0].[ThreatId]
  LEFT JOIN [ExternalLink] AS [e1] ON [t3].[Id] = [e1].[ThreatId]
  WHERE [t0].[NotApplicable] = CAST(0 AS bit) AND ([t0].[Verified] = CAST(0 AS bit) OR [t0].[Implemented] = CAST(0 AS bit))

Creating an extension method like this also works:

public static IQueryable<ProductSummaryDtoHelper> QueryProductSummary(this IQueryable<Product> query)
{
    var summary = query.Select(product => new ProductSummaryDtoHelper()
    {
        UnresolvedThreatAndCountermeasures =
            product.ThreatAndCountermeasures.Where(tac => !tac.NotApplicable && !(tac.Verified && tac.Implemented)).Select(
                vuln => new ProductSummaryVulnerabilityDtoHelper()
                {
                    ExternalLinksHelper = vuln.ExternalLinks.MapToExternalLinkDto(),
                    ExternalLinksHelperThreat = vuln.Threat.ExternalLinks.MapToExternalLinkDto(),
                    ExternalLinksHelperThreatFork = vuln.Threat.Fork.ExternalLinks.MapToExternalLinkDto()
                })
    });

    return summary;
}

public static IEnumerable<ExternalLinkDto> MapToExternalLinkDto(this List<ExternalLink> externalLinks)
{
    var summary = externalLinks.Select(externalLink => new ExternalLinkDto()
    {
        Id = externalLink.Id,
        Link = externalLink.Link,
        LinkType = externalLink.LinkType,
        LinkText = externalLink.LinkText
    });

    return summary;
}

However the SQL generated will pick up every extra field and I do not want that:

SELECT [t0].[Id], [t1].[Id] AS [Id0], [t3].[Id] AS [Id1], [e].[Id] AS [Id2], [e].[Created], [e].[CreatedById], [e].[Guid], [e].[Link], [e].[LinkText], [e].[LinkType], [e].[PeriodEnd], [e].[PeriodStart], [e].[ProductId], [e].[ThreatAndCountermeasureId], [e].[ThreatId], [e].[Updated], [e].[UpdatedById], [e].[Version], [e0].[Id] AS [Id3], [e0].[Created] AS [Created0], [e0].[CreatedById] AS [CreatedById0], [e0].[Guid] AS [Guid0], [e0].[Link] AS [Link0], [e0].[LinkText] AS [LinkText0], [e0].[LinkType] AS [LinkType0], [e0].[PeriodEnd] AS [PeriodEnd0], [e0].[PeriodStart] AS [PeriodStart0], [e0].[ProductId] AS [ProductId0], [e0].[ThreatAndCountermeasureId] AS [ThreatAndCountermeasureId0], [e0].[ThreatId] AS [ThreatId0], [e0].[Updated] AS [Updated0], [e0].[UpdatedById] AS [UpdatedById0], [e0].[Version] AS [Version0], [e1].[Id] AS [Id4], [e1].[Created] AS [Created1], [e1].[CreatedById] AS [CreatedById1], [e1].[Guid] AS [Guid1], [e1].[Link] AS [Link1], [e1].[LinkText] AS [LinkText1], [e1].[LinkType] AS [LinkType1], [e1].[PeriodEnd] AS [PeriodEnd1], [e1].[PeriodStart] AS [PeriodStart1], [e1].[ProductId] AS [ProductId1], [e1].[ThreatAndCountermeasureId] AS [ThreatAndCountermeasureId1], [e1].[ThreatId] AS [ThreatId1], [e1].[Updated] AS [Updated1], [e1].[UpdatedById] AS [UpdatedById1], [e1].[Version] AS [Version1], [t0].[ProductId] AS [ProductId2]
FROM [ThreatAndCountermeasure] AS [t0]
INNER JOIN [Threats] AS [t1] ON [t0].[ThreatId] = [t1].[Id]
LEFT JOIN [Threats] AS [t3] ON [t1].[ForkId] = [t3].[Id]
LEFT JOIN [ExternalLink] AS [e] ON [t0].[Id] = [e].[ThreatAndCountermeasureId]
LEFT JOIN [ExternalLink] AS [e0] ON [t1].[Id] = [e0].[ThreatId]
LEFT JOIN [ExternalLink] AS [e1] ON [t3].[Id] = [e1].[ThreatId]
WHERE [t0].[NotApplicable] = CAST(0 AS bit) AND ([t0].[Verified] = CAST(0 AS bit) OR [t0].[Implemented] = CAST(0 AS bit)

Answer 1

Update:

It turned out all I was missing was AsQueryable().

https://stackoverflow.com/a/20379242/3850405

After adding this everything worked as I wanted.

public static IQueryable<ProductSummaryDtoHelper> QueryProductSummary(this IQueryable<Product> query)
{
    var summary = query.Select(product => new ProductSummaryDtoHelper()
    {
        UnresolvedThreatAndCountermeasures =
            product.ThreatAndCountermeasures.Where(tac => !tac.NotApplicable && !(tac.Verified && tac.Implemented)).Select(
                vuln => new ProductSummaryVulnerabilityDtoHelper()
                {
                    ExternalLinksHelper = vuln.ExternalLinks.AsQueryable().Select(ExternalLinkDto.Conversion),
                    ExternalLinksHelperThreat = vuln.Threat.ExternalLinks.AsQueryable().Select(ExternalLinkDto.Conversion),
                    ExternalLinksHelperThreatFork = vuln.Threat.Fork.ExternalLinks.AsQueryable().Select(ExternalLinkDto.Conversion)
                })
    });

    return summary;
}

Complete small code 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; }
}

Thanks to @maumar and @stevendarby:

https://github.com/dotnet/efcore/issues/30726

Original:

I was stuck on this for way to long. I suddenly realized I had missed to compile the expression tree into executable code...

https://learn.microsoft.com/en-us/dotnet/api/system.linq.expressions.expression-1.compile?view=net-7.0#system-linq-expressions-expression-1-compile

public static IQueryable<ProductSummaryDtoHelper> QueryProductSummary(this IQueryable<Product> query)
{
    var compiledFunction = ExternalLinkDto.Conversion.Compile();

    var summary = query.Select(product => new ProductSummaryDtoHelper()
    {
        UnresolvedThreatAndCountermeasures =
            product.ThreatAndCountermeasures.Where(tac => !tac.NotApplicable && !(tac.Verified && tac.Implemented)).Select(
                vuln => new ProductSummaryVulnerabilityDtoHelper()
                {
                    ExternalLinksHelper = vuln.ExternalLinks.Select(compiledFunction),
                    ExternalLinksHelperThreat = vuln.Threat.ExternalLinks.Select(compiledFunction),
                    ExternalLinksHelperThreatFork = vuln.Threat.Fork.ExternalLinks.Select(compiledFunction)
                })
    }); ;

    return summary;
}

This did however cause a new error:

System.ArgumentException: 'Expression of type 'System.Func2[MyProject.Models.Entities.ExternalLink,MyProject.Models.DTO.ExternalLinkDto]' cannot be used for parameter of type 'System.Linq.Expressions.Expression1[System.Func2[MyProject.Models.Entities.ExternalLink,MyProject.Models.DTO.ExternalLinkDto]]' of method 'System.Linq.IQueryable1[MyProject.Models.DTO.ExternalLinkDto] Select[ExternalLink,ExternalLinkDto](System.Linq.IQueryable1[MyProject.Models.Entities.ExternalLink], System.Linq.Expressions.Expression1[System.Func`2[MyProject.Models.Entities.ExternalLink,MyProject.Models.DTO.ExternalLinkDto]])' (Parameter 'arg1')'

The final working version looked like this:

public static IQueryable<ProductSummaryDtoHelper> QueryProductSummary(this IQueryable<Product> query)
{
    var compiledFunction = ExternalLinkDto.Conversion.Compile();

    var summary = query.Select(product => new ProductSummaryDtoHelper()
    {
        UnresolvedThreatAndCountermeasures =
            product.ThreatAndCountermeasures.Where(tac => !tac.NotApplicable && !(tac.Verified && tac.Implemented)).Select(
                vuln => new ProductSummaryVulnerabilityDtoHelper()
                {
                    ExternalLinksHelper = vuln.ExternalLinks.Select(x => compiledFunction(x)),
                    ExternalLinksHelperThreat = vuln.Threat.ExternalLinks.Select(x => compiledFunction(x)),
                    ExternalLinksHelperThreatFork = vuln.Threat.Fork.ExternalLinks.Select(x => compiledFunction(x)),
                })
    }); ;

    return summary;
}

This does not query efficiently either:

SELECT [t0].[Id], [t1].[Id] AS [Id0], [t3].[Id] AS [Id1], [e].[Id] AS [Id2], [e].[Created], [e].[CreatedById], [e].[Guid], [e].[Link], [e].[LinkText], [e].[LinkType], [e].[PeriodEnd], [e].[PeriodStart], [e].[ProductId], [e].[ThreatAndCountermeasureId], [e].[ThreatId], [e].[Updated], [e].[UpdatedById], [e].[Version], [e0].[Id] AS [Id3], [e0].[Created] AS [Created0], [e0].[CreatedById] AS [CreatedById0], [e0].[Guid] AS [Guid0], [e0].[Link] AS [Link0], [e0].[LinkText] AS [LinkText0], [e0].[LinkType] AS [LinkType0], [e0].[PeriodEnd] AS [PeriodEnd0], [e0].[PeriodStart] AS [PeriodStart0], [e0].[ProductId] AS [ProductId0], [e0].[ThreatAndCountermeasureId] AS [ThreatAndCountermeasureId0], [e0].[ThreatId] AS [ThreatId0], [e0].[Updated] AS [Updated0], [e0].[UpdatedById] AS [UpdatedById0], [e0].[Version] AS [Version0], [e1].[Id] AS [Id4], [e1].[Created] AS [Created1], [e1].[CreatedById] AS [CreatedById1], [e1].[Guid] AS [Guid1], [e1].[Link] AS [Link1], [e1].[LinkText] AS [LinkText1], [e1].[LinkType] AS [LinkType1], [e1].[PeriodEnd] AS [PeriodEnd1], [e1].[PeriodStart] AS [PeriodStart1], [e1].[ProductId] AS [ProductId1], [e1].[ThreatAndCountermeasureId] AS [ThreatAndCountermeasureId1], [e1].[ThreatId] AS [ThreatId1], [e1].[Updated] AS [Updated1], [e1].[UpdatedById] AS [UpdatedById1], [e1].[Version] AS [Version1], [t0].[ProductId] AS [ProductId2]
FROM [ThreatAndCountermeasure] AS [t0]
INNER JOIN [Threats] AS [t1] ON [t0].[ThreatId] = [t1].[Id]
LEFT JOIN [Threats] AS [t3] ON [t1].[ForkId] = [t3].[Id]
LEFT JOIN [ExternalLink] AS [e] ON [t0].[Id] = [e].[ThreatAndCountermeasureId]
LEFT JOIN [ExternalLink] AS [e0] ON [t1].[Id] = [e0].[ThreatId]
LEFT JOIN [ExternalLink] AS [e1] ON [t3].[Id] = [e1].[ThreatId]
WHERE [t0].[NotApplicable] = CAST(0 AS bit) AND ([t0].[Verified] = CAST(0 AS bit) OR [t0].[Implemented] = CAST(0 AS bit))