LINQ to SQL Join orderby

Question

i am new to LINQ and joins, so Please forgive me if I am asking it wrong. I have two tables

Table1

id   name    date
1    Mike    20-10-15
2    John    21-10-15
3    Sam     23-10-15

Table2

id   name   date
1    Ashle  19-10-15
2    Lily   21-10-15
3    Jeni   22-10-15
4    April  23-10-15

I need 5 records using Joins and should be orderby Date, most recent records.

Can you guys help me, I really need to figure out how Joins works with orderby. Thanks

EDIT: They are two different tables so no foreign key, so I think I can't use Join, so so far what I have done is like this

var combinddata = (from t1 in db.Table1
                        select t1.id)
                            .Concat(from t2 in db.Table2
                                    select t2.id);

I don't know how to get only 5 records how to compare records from both tables on DateTime base.

Output should be

Sam
April
Jeni
John
Lily

No you are NOT "asking it wrong" but you need to show first what you have already tried. — Rahul Singh, Oct 23 '15 at 12:09
Possible duplicate of [What is the syntax for an inner join in LINQ to SQL?](http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql) — Perttu Haliseva, Oct 23 '15 at 12:09
you have to use `Concat`. then `OrderByDescending` date. then `Take` first 5. — M.kazem Akhgary, Oct 23 '15 at 12:14
See webpage : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Oct 23 '15 at 12:20
I edited the question, Can you guys please see it again. Thanks — aadi1295, Oct 23 '15 at 12:28
@MukeshKalgude: I updated the question with possible output. can you please check. Thanks — aadi1295, Oct 23 '15 at 12:51

Gert Arnold · Accepted Answer · 2015-10-23T16:53:42.800

You can concatenate equal anonymous types from different tables. If you also select the dates, you can sort by them, in descending order, and take the first 5 records:

Table1.Select (t1 => 
    new
    {
        Id = t1.Id,
        Name = t1.Name,
        Date = t1.Date
    }
).Concat(
Table2.Select (t2 => 
    new
    {
        Id = t2.Id,
        Name = t2.Name,
        Date = t2.Date
    }
))
.OrderByDescending (x => x.Date).Take(5)

Note that this gives precedence to items in Table1. If item 5 and 6 in the concatenated result are on the same date, but from Table1 and Table2, respectively, you only get the item from Table1.

If you want, you can select only the names from this result, but I assume that your output only shows the intended order of record, not the exact expected result.

Aruna Udayanag · Answer 2 · 2015-10-23T12:32:31.223

0

var query = 
    from Table1 in table1
    join Table2 in table2 on table1.id equals table2.id 
    orderby table1.date ascending 
    select table1.date;

edited Oct 23 '15 at 12:32

answered Oct 23 '15 at 12:23

Aruna Udayanag

69
4

They are two different tables so no foreign key, so I think I can't use Join, so why are you use join.... – Mukesh Kalgude Oct 23 '15 at 12:41
@MukeshKalgude: I am sorry, actually I didn't mention it earlier and used Join in question title. Actually I wasn't sure what to use. Join or Concat. – aadi1295 Oct 23 '15 at 12:43

score 0 · Answer 3 · answered Oct 23 '15 at 12:56

0

Try this way

var combinddata = (from t1 in db.Table1
                        select t1.Name)
                            .Concat(from t2 in  db.Table2 
                                    select t2.Name).OrderByDescending(x => x.date).Take(5);

answered Oct 23 '15 at 12:56

Mukesh Kalgude

4,814
2
17
32

LINQ to SQL Join orderby

3 Answers3