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 Id
s (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; }
}