LINQ, select from multi table

Question

I have two tables: Questions and Educations, there isn't any relation between these tables.

How can I select the combination of the last 10 (order by date) item from both these tables?

Can you elaborate? Atleast show column names from each table. — Rahul Singh, Aug 03 '15 at 14:04
If there is no relation between the tables, how are we supposed to select "a combination"? And more importantly, how in heavens name has this q had 5 upvotes :| — Jamiec, Aug 03 '15 at 14:04
You can perform a [join](http://stackoverflow.com/questions/16025287/entity-framework-query-for-inner-join) — jrummell, Aug 03 '15 at 14:05

score 0 · Answer 1 · answered Aug 03 '15 at 14:17

0

You want something like

 var result = questions.OrderByDescending(i => i.id).Take(5).Union(education.OrderByDescending(i => i.id).Take(5));

answered Aug 03 '15 at 14:17

BugFinder

16,714
4
38
50

This would not work if there was 10 from one table and 0 from the other – William Moore Aug 03 '15 at 14:19
I did say "like" :P as I picked ID not date too – BugFinder Aug 03 '15 at 14:20
does not contain a definition for `OrderByDescending` – prince Aug 03 '15 at 14:20
LinQ does. if you have tables you would need tablename.AsEnumerable().Orderby...... – BugFinder Aug 03 '15 at 14:21
@BugFinder I user this `var result = db.questions.AsEnumerable().OrderByDescending(i => i.Id).Take(5).Union(db.education.AsEnumerable().OrderByDescending(i => i.Id).Take(5)); ` , but i have an error : `'System.Collections.Generic.IEnumerable' does not contain a definition for 'Union' and the best extension method overload 'System.Linq.ParallelEnumerable.Union(System.Linq.ParallelQuery, System.Collections.Generic.IEnumerable)' has some invalid arguments ` – prince Aug 03 '15 at 14:37
So. you've now got other things in play you didnt mention. in that "MyPrj.questions" is an undefined thing.. you really are making this hard for people to help you – BugFinder Aug 03 '15 at 14:46
@BugFinder I believe that the error message from @prince is because you can't preform a union between two different types (see https://msdn.microsoft.com/en-us/library/vstudio/bb341731(v=vs.100).aspx). Hence a union between enumerations of `MyPrj.questions` and `MyPrj.Education` would be undefined. They must be enumerable of the same type, hence in my answer, in the select I created objects of the same type, which can have `Union` applied. – William Moore Aug 03 '15 at 15:46
Thats why its more complicated. If it were from two datatables, it works. It has to be the same type. – BugFinder Aug 03 '15 at 15:47

William Moore · Answer 2 · 2015-08-03T14:24:02.657

In LINQ you can't join two tables with o relationship, which would be needed to preform your query. You would be better of doing a two LINQ to SQL queries to select the last 10 of each table into similar structure, and then taking these two enumerations, doing a union and taking the last 10 of that.

If you used a structure like:

public struct QuestionsEducationsJoin
{
    public DateTime date;
    public int QuestionID;
    public string Question;//...;
    public int EducationID;
    public string Education;//...;
}

You can then select from both tables into this structure:

var questionEnumeration = database.Questions
   .OrderByDescending(question => question.DateField)
   .Take(10)
   .Select(question => new QuestionsEducationsJoin()
       {
           date = question.DateField;
           //...
       });

var educationEnumeration; //...

var outputEnumeration = questionEnumberation
    .Union(educationEnumeration)
    .OrderByDescending(x => x.date)
    .Take(10);

score 0 · Answer 3 · answered Aug 03 '15 at 14:19

The answer depends on whether or not you need the resulting list to be polymorphic (a list of objects that share a common interface exposing common properties to be manipulated) or a simple list of objects that you don't easily know how to cast.

If there are fields common to both that you want to work with, create an interface of these common attributes and have both classes implement the interface. For instance, the interface could be

Interface iBrainStuff
{
    public DateTime DateCreated;
    public int AreaOfExpertise;
    public string Description;

}

Then you simply implement the interface in each class:

public class question: iBrainStuff


public class education: iBrainStuff

Then you create your results list

List<iBrainStuff> results = new List<iBrainStuff>();

then you

results.AddRange(context.educations.OrderByDescending(x => x.DateCreated).Take(5);

results.AddRange(context.questions.OrderByDescending(x => x.DateCreated).Take(5);

And that puts the top ten (5 from each) in the results list. And the results in the results list can all be interrogated for their iBrainStuff interface fields:

Interface iBrainStuff
{
    public DateTime DateCreated;
    public int AreaOfExpertise;
    public string Description;

}

LINQ, select from multi table

3 Answers3