EF Core One To Many Include

asp.net-core-mvc c# entity-framework-core linq

Question

I work on a ASP.NET Core MVC Website with EF-CORE, in my database, I have a "Doc" table and a "Signature" Table, one Doc can have many Signatures and one Signature can have only on Doc, Here's my Code First Entity Model :

Doc :

public class Doc
{
    [Key]
    public int DocID { get; set; }

    [Required]
    public int DocTypeID { get; set; }
    [ForeignKey("DocTypeID")]
    public virtual DocType DocType { get; set; }

    [Required]
    public int Version { get; set; }

    [Required]
    public Byte[] Data { get; set; }

    [ForeignKey("DocID")]
    public List<Signature> Signatures { get; set; }
}

Signature : public class Signature {

    //FK ITPolicyVersion
    [Key]
    public int DocID { get; set; }
    [ForeignKey("DocID")]
    public virtual Doc Doc { get; set; }

    //FK EmployeeID
    [Key]
    public int EmployeeID { get; set; }
    [ForeignKey("EmployeeID")]
    public virtual Employee Employee { get; set; }


    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:dd-MM-yyyy}", ApplyFormatInEditMode = true)]
    [Display(Name = "Signature Date")]
    public DateTime? SignatureDate { get; set; }


    public String Value { get; set; }
}

But when I use this request :

_applicationDBContext.Doc
                            .Include(d => d.DocType)
                            .Include(d => d.Signatures)
                            .OrderBy(d => d.DocType)
                            .ToList();

d.Signatures is always null, and I cannot figure why.

Here's what I'm trying to do in SQL :

Select * from Doc d
join DocType dt on dt.DocTypeID = d.DocTypeID
join Signature s on s.DocID = d.DocID
join Employee e on e.EmployeeID = s.EmployeeID

And this work well in SQL but not with LINQ

EDIT

This work :

List<Doc> docs = _applicationDBContext.Doc
                            .Include(d => d.Signatures)
                                .ThenInclude(s=>s.Employee)
                            .Include(d => d.DocType)
                            .ToList();

But not this :

List<Doc> docs = _applicationDBContext.Doc
                                .Include(d => d.Signatures)
                                    .ThenInclude(s=>s.Employee)
                                .Include(d => d.DocType)
                                .OrderBy(d => d.DocType)
                                .ToList();

Signature became empty

How can I order this list by DocType ?

1
1
5/29/2018 1:42:15 PM

Accepted Answer

There was in fact an error on the orderby but it wasn't throw : DocType is an other table and doesn't implement IComparable so I just change the request like that :

List<Doc> docs = _applicationDBContext.Doc
                                .Include(d => d.Signatures)
                                    .ThenInclude(s=>s.Employee)
                                .Include(d => d.DocType)
                                .OrderBy(d => d.DocType.Name)
                                .ToList();

And it work perfectly.

1
5/29/2018 2:08:57 PM

Popular Answer

Take a look at the ef core page on relationships.

You should be able to simplify to something like this.

Keep in mind lots of things work by convention so naming is important (eg using Id for the entity key is picked up automatically by entity framework)

Where you have backwards navigation properties on 'child' objects you need the foreign key attribute and id property OR to use the Fluent API.

public class Doc
{
    public int Id { get; set; }

    public DocType DocType { get; set; }

    [Required]
    public int Version { get; set; }

    [Required]
    public Byte[] Data { get; set; }

    public List<Signature> Signatures { get; set; }
}

public class Signature {

    public int Id { get; set; }

    //backwards navigation
    public int DocForeignKey {get;set;}
    [ForeignKey("DocForeignKey")]
    public Doc Doc { get; set; }

    public Employee Employee { get; set; }

    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:dd-MM-yyyy}", ApplyFormatInEditMode = true)]
    [Display(Name = "Signature Date")]
    public DateTime? SignatureDate { get; set; }

    public String Value { get; set; }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow