Linq query to select data from table and join 2 tables

Question

I am trying to select everything from a table and then make a join with two other tables, from which I need just some of the columns. The final list will be populated into a DevExpress GridControl. The main table that I take the data from has these columns:

Id | ScheduleId | AccountId | Description

I can easily select everything from this table and return. Here is my list:

public IList<StatusDescription> getStatusDescription()
{
    var listOfItems = from e in context.StatusDescription select e;
    return listOfItems.ToList<StatusDescription>();
}

The other two tables are Schedules and Accounts, they look as follows:

Id | ScheduleName | DateFrom | DateTo

and

Id | AccountName | AccountCode

I would like to join the DateFrom and DateTo from the Schedule table and the AccountCode from the Account table. Is it possible to get all that into a single list. I can easily bind it later to the GridControl

I don't see any problem, have a look at the following http://stackoverflow.com/a/32465/1099716 — Access Denied, Jun 30 '14 at 07:59

score 3 · Accepted Answer · answered Jun 30 '14 at 08:00

3

var listOfItems = from e in context.StatusDescription
                  join s in context.Schedules on e.ScheduleId equals s.Id
                  join a in context.Accounts on e.AccountId equals a.Id
                  select new 
                  {
                      Description = e.Description,
                      ScheduleStart = s.DateFrom,
                      ScheduleEnd = s.DateTo,
                      AccountCode = a.AccountCode
                  }

answered Jun 30 '14 at 08:00

James

80,725
18
167
237

I accepted the answer because it is a solution to the question. But do you know what should the return type be, because trying to return `IList` gives an error(I understand why it does it). – Apostrofix Jun 30 '14 at 08:46
1

@Ivo In the example the return type is an anonymous object, if you need to return a specific type then you would need to create a type which accommodates those particular fields `select new QueryType { ... }`. You can't expect `StatusDescription` because it doesn't have properties like `ScheduleStart` / `ScheduleEnd` etc. – James Jun 30 '14 at 08:57
Thank you for the comment, I was hoping to avoid creating a new class, but it's done now. – Apostrofix Jun 30 '14 at 09:42

score 0 · Answer 2 · answered Jun 30 '14 at 08:27

0

var Query = from p in context.StatusDescription select new { p.Id, p.Description, p.Schedule.DateFrom, p.Schedule.DateTo, p.Account.AccountCode };

hope it helps

answered Jun 30 '14 at 08:27

Nima Derakhshanjan

1,380
9
24
37

yes this is a simple select and it mean's we do not need 'join' in this case :) – Nima Derakhshanjan Jun 30 '14 at 08:40

Linq query to select data from table and join 2 tables

2 Answers2

Linked