CODEWITHSUNDEEP
c#asp.net-mvclinq
Tommy Lee Jones w Sciganym
Tommy Lee Jones w Sciganym

Reputation: 160

Why LINQ returns other column value than expected?

My goal is to retrieve ParentTypeId for single ParentId.
I keep searching and trying varius approaches
but I end up all my queries return ParentId rather than ParentTypeId.

My model has these 2 tables:

public class Parent
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Index("ParentIdIndex", IsUnique = true)]
    public int ParentId  { get; set; }

    public string ParentName {get; set;}

    public virtual ParentType ParentType { get; set; }
}

public class ParentType 
{

    [Key, ForeignKey("Parent")]
    public int ParentTypeId { get; set; }

    public string ParentTypeName { get; set; }

    public virtual Parent Parent { get; set; }
}

This SQL query returns correct result:

select 
ParentId,
ParentType_ParentTypeId
FROM [myDB].[dbo].[Parents]
Where ParentId = 5

Which is one row: (ParentId = 5, ParentTypeId = 6)

But when use LINQ query in c# method it returns ParentId value,
not ParentTypeId value. 

        var query = (
                    from t1 in db.Parents
                    where (t1.ParentId == ParentId)
                    select t1.ParentType.ParentTypeId
                    ).FirstOrDefault();

Why?

Upvotes: 2

Views: 88

Answers (1)

TriV
TriV

Reputation: 5148

You created foreign key incorrectly.

ParentTypeId must be foreign key of Parent table.

And primary table must be ParentType.

See my example:

Parent class

public class Parent
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Index("ParentIdIndex", IsUnique = true)]
    public int ParentId { get; set; }

    public string ParentName { get; set; }

    [ForeignKey("ParentType")]
    public int ParentTypeId { get; set; }

    public virtual ParentType ParentType{ get; set; }
}

and ParentType class

public class ParentType
{
    [Key]
    public int ParentTypeId { get; set; }

    public string ParentTypeName { get; set; }

    public virtual IEnumerable<Parent> Parents { get; set; }
}

You can get ParentTypeId easily like this

var parentItem = (
                from t1 in db.Parents
                where (t1.ParentId == 12345)
                select t1
                ).FirstOrDefault();
// parentItem.ParentTypeId

Upvotes: 1

Related Questions