Linq Any always returns true

2019-05-27 08:35发布

问题:

I have used Linq to Entities for many years, but this is the first time I'm facing this problem. I have Tips and Items tables, which each tip can have many items.I have only 3 items in the database. when editing an Item, I want to make sure that GivenId field is unique for items in having the same Tip. I use this:

    Item item =db.Items.FirstOrDefault(c => c.Id == id);
    if (item != null)
    {
         if (db.Items
               .Any(x => x.GivenId == newGivenId && 
                         x.Id != item.Id && 
                         x.TipId == item.TipId))
         {
              //newGivenId is either different or if it is the same, 
              //it belongs to the same item
         } 
         else
         {
            //newGivenId already exists for an Item in the same tip
         }
   }

but

    db.Items
       .FirstOrDefault(x => x.GivenId == newGivenId && 
                            x.Id != item.Id &&  
                            x.TipId == item.TipId);

returns null as expected.

I know that I can use it like:

Item it = db.Items
            .FirstOrDefault(x => x.GivenId == newGivenId && 
                                 x.Id != item.Id && 
                                 x.TipId == item.TipId);

    if(it==null) 
    {

    }
    else
    {

    }

But I just want to find what is wrong with Any.

P.S.: All Ids (3 of them) are typeof(int)

EDIT:

this is the generated sql Query:

DECLARE @p__linq__0 AS SQL_VARIANT;
DECLARE @p__linq__1 AS SQL_VARIANT;
DECLARE @p__linq__2 AS SQL_VARIANT;
SET @p__linq__0 = NULL;
SET @p__linq__1 = NULL;
SET @p__linq__2 = NULL;

SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Items] AS [Extent1]
        WHERE ([Extent1].[GivenId] = @p__linq__0) AND ([Extent1].[Id] <> @p__linq__1) AND (([Extent1].[TipId] = @p__linq__2) OR (([Extent1].[TipId] IS NULL) AND (@p__linq__2 IS NULL)))
    )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

EF Generated classes:

public partial class Item
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Item()
    {
        this.Parts = new HashSet<Part>();
    }

    public int Id { get; set; }
    public Nullable<int> TipId { get; set; }
    public Nullable<int> GivenId { get; set; }
    public string FileId { get; set; }
    public Nullable<bool> Carve { get; set; }
    public Nullable<bool> Mina { get; set; }
    public Nullable<bool> Deleted { get; set; }

    public virtual Tip Tip { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Part> Parts { get; set; }
}

public partial class Tip
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Tip()
    {
        this.Items = new HashSet<Item>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public Nullable<int> ModelId { get; set; }
    public Nullable<bool> Deleted { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Item> Items { get; set; }
    public virtual Model Model { get; set; }
}