EF one to many Relationship navigate

68 views Asked by At

I have one to many relation between two tables UnAuthEvents and UnAuthExceptionLists. For all my project data requirements which involves foreign key references, i have used "Include" to include the navigation properties. In this case when i do like below, it doesnt recognize UnAuthExceptionLists

 var exception = DB.UnAuthEvents.
                                        Include(i => i.UnAuthExceptionLists)
                                        .Select(u => new
                                        {
                                            u.EventID,
                                            u.UnAuthExceptionLists.ExceptionID
                                        }
                                        );

At the same time, this method of join works

var test = (from e in DB.UnAuthEvents
                       join a in DB.UnAuthExceptionLists
                       on e.EventID equals a.EventID
                       where e.EventID == 1
                       select new
                       {
                           e.EventID,
                           a.ExceptionID
                       });

Question 1: Though i have working model available now, i would like to know the logic behind this

Question 2: As per my project requirement, i would like to get the below JSON data format

[{"EventID":1,"UnAuthExceptionLists":[{"ExceptionID":1},{"ExceptionID":2}]}]

But my code produces the output like this

[{"EventID":1,"ExceptionID":1},{"EventID":1,"ExceptionID":2}]
1

There are 1 answers

0
TechQuery On

Got the solution for this

var test = from e in DB.UnAuthEvents
                       join a in DB.UnAuthExceptionLists
                       on e.EventID equals a.EventID
                       where e.EventID == 1
                       select new ExceptionDTO
                       {
                           ExceptionTypeID = e.EventID ,
                           unauthTest = DB.UnAuthExceptionLists.Select(b=>new ExceptionListDTO {KeyFromOther= b.KeyFromOther,EventID = b.EventID}).Where(i=>i.EventID==e.EventID).ToList()

                       };

ExceptionDTO class have two properties, one is ExceptionTypeID and another is an ICollection of ExceptionListDTO custom class

But my first question dont have an answer:(