LINQ to Entities 1 to many query
-
Hi All, I'm currently into my first project using LINQ and Entities and fairly early on in the project I'm stumbling. I have two tables and I've created a Data Access project which contains and edmx file containing objects which map to these tables. I can use this to insert into the DB fine. The structure is such that table Event has an EventID column and this joins to a table EventAttribute on EventAttribute.EventID and there can be many EventAttributes for each event. What I want to do is query the DB using my entities to return the first 1 event based on the lowest EventID and all EventAttributes for that Event. what I have at the moment is
using (AuditLoggerEntities ale = new AuditLoggerEntities())
{
var e = from Ev in ale.Events join EventAttribute in ale.EventAttributes on Ev.EventID equals EventAttribute.EventID select Ev;// other stuff}
but this (obviously) returns all events and associated EventAttributes but by limiting this using Take(1) also limits EventAttributes to the top 1. So after much googling and trying various options I'm stuck so can someone help me write a query that returns 1 event and all Associated EventAttributes? Thanks Paul
-
Hi All, I'm currently into my first project using LINQ and Entities and fairly early on in the project I'm stumbling. I have two tables and I've created a Data Access project which contains and edmx file containing objects which map to these tables. I can use this to insert into the DB fine. The structure is such that table Event has an EventID column and this joins to a table EventAttribute on EventAttribute.EventID and there can be many EventAttributes for each event. What I want to do is query the DB using my entities to return the first 1 event based on the lowest EventID and all EventAttributes for that Event. what I have at the moment is
using (AuditLoggerEntities ale = new AuditLoggerEntities())
{
var e = from Ev in ale.Events join EventAttribute in ale.EventAttributes on Ev.EventID equals EventAttribute.EventID select Ev;// other stuff}
but this (obviously) returns all events and associated EventAttributes but by limiting this using Take(1) also limits EventAttributes to the top 1. So after much googling and trying various options I'm stuck so can someone help me write a query that returns 1 event and all Associated EventAttributes? Thanks Paul
how about...
var e = ale.Events.OrderBy(e => e.EventID).First().EventAttributes;
e will now be a list of EventAttributes for the first Event when events are ordered ascending by EventID Also, if EventID is an AutoIncrement you can assume the entries will be in ascending order anyway and probably just leave out the OrderBy part...
var e = ale.Events.First().EventAttributes;
I may or may not be responsible for my own actions
-
how about...
var e = ale.Events.OrderBy(e => e.EventID).First().EventAttributes;
e will now be a list of EventAttributes for the first Event when events are ordered ascending by EventID Also, if EventID is an AutoIncrement you can assume the entries will be in ascending order anyway and probably just leave out the OrderBy part...
var e = ale.Events.First().EventAttributes;
I may or may not be responsible for my own actions
That's excellent thanks. The change to LINQ is a bit of a paradigm shift and I certainly need more practise! Thanks again for your help. Paul