Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

WHERE ... IN ... for .Contains(.Convert()) expressions issue #8207

Closed
Remleo opened this issue Apr 18, 2017 · 4 comments
Closed

WHERE ... IN ... for .Contains(.Convert()) expressions issue #8207

Remleo opened this issue Apr 18, 2017 · 4 comments
Assignees

Comments

@Remleo
Copy link

Remleo commented Apr 18, 2017

Code to reproduce:

// Models
public class ProductCard
{
        public virtual int Id { get; set; }
        //...
        public virtual int? GroupId { get; set; }
        public virtual ProductGroup Group { get; set; }
        //...
}

public class ProductGroup
{
        public virtual int Id { get; set; }
        //...
}
// Code 1
var someIds = new [] {1, 2, 3}; // Have some Ids from method's parameter for example
dbContext.ProductCards.Where(
                    card => someIds.Contains((int)card.GroupId)).Load();

// Code 2
dbContext.ProductCards.Where(
                    card => dbContext.ProductGroups.Select(group => group.Id).Where(...)
                                        .Contains( (int)card.GroupId) 
).Load();

The Code 1 throws warning:
Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Warning: The LINQ expression 'Contains(Convert([card].GroupId))' could not be translated and will be evaluated locally.

But the Code 2 generates correct SQL (ignores .Convert()):

SELECT [card].[Id], ...
FROM [ProductCard] AS [card]
WHERE [card].[GroupId] IN (
    SELECT [group].[Id]
    FROM [ProductGroup] AS [group]
)

The problem is in convertation from int? to int. The PK is int, but the FK is int?.

I can use .Cast<int?> via extra local var, but it's an extra work for CPU and memory. LINQ to SQL compiler can simply ignore .Convert().

In the EF Core 1.1.0-preview1-final works fine! This problem appeared in version 1.1.1.

@maumar maumar self-assigned this Apr 19, 2017
@ajcvickers ajcvickers added this to the 1.1.2 milestone Apr 19, 2017
@ajcvickers
Copy link
Contributor

Putting in 1.1.2 since it might be a regression, but we should check against 1.1.0 RTM.

@maumar
Copy link
Contributor

maumar commented Apr 19, 2017

@Remleo I was able to reproduce the issue in 1.1.1 and patch bits (1.1.2), and you are right - it is due to the convert not being removed when we try to translate the predicate. However, the scenario was also failing for me in 1.1.0-preview1 (and from looking at the 1.1.0-preview1 code, the relevant code looks the same).

I used the following code as repro:

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();
            }

            using (var ctx = new MyContext())
            {
                var someIds = new int[] { 1, 2, 3 }; // Have some Ids from method's parameter for example
                ctx.ProductCards.Where(
                                    card => someIds.Contains((int)card.GroupId)).Load();

                // Code 2
                ctx.ProductCards.Where(
                                    card => ctx.ProductGroups.Select(group => group.Id).Where(g => g > 7)
                                                        .Contains((int)card.GroupId)
                ).Load();
            }
        }
    }

    public class ProductCard
    {
        public virtual int Id { get; set; }
        public virtual int? GroupId { get; set; }
        public virtual ProductGroup Group { get; set; }
    }

    public class ProductGroup
    {
        public virtual int Id { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<ProductGroup> ProductGroups { get; set; }
        public DbSet<ProductCard> ProductCards { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.;Database=Repro8207;Trusted_Connection=True;MultipleActiveResultSets=True");
        }
    }

Can you double check that the problem actually was not present in 1.1.0-preview (and therefore it is a regression)? And if so, could you provide us with the full code listing that you used? (or point out the differences between your project and the one I posted above). The issue has been fixed already in the current bits (see #6937). It's just a question whether it should be considered for 1.1.2 patch or not.

@Remleo
Copy link
Author

Remleo commented Apr 20, 2017

@maumar Sorry, my mistake. You are right, the problem was really present in 1.1.0-preview too.

@maumar
Copy link
Contributor

maumar commented Apr 20, 2017

@Remleo thank you for double checking. In that case, closing this as a dupe of #6937

@maumar maumar closed this as completed Apr 20, 2017
@maumar maumar modified the milestones: 2.0.0, 1.1.2, 2.0.0-preview1 Apr 20, 2017
@divega divega removed this from the 2.0.0-preview1 milestone May 8, 2017
@ajcvickers ajcvickers reopened this Oct 16, 2022
@ajcvickers ajcvickers closed this as not planned Won't fix, can't repro, duplicate, stale Oct 16, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants