select most recent data from multiple colums

Question

0.00/5 (No votes)

See more:

Hi,

I'm trying to select the 30 latest entries in my database, but I want to select them from 4 different tables.

Is there a way to do this clean and quick using linq?

At the end of the query I want to store these objects in a list.

Thanks in advance!

Posted 25-Apr-13 3:12am

Sam Van den Bossche

Updated 5-May-13 20:57pm

lukeer

v2

Add a Solution

Comments

CHill60 25-Apr-13 11:08am

If you are accessing your database why not use an OleDbDataReader or SqlDataReader as appropriate and write the sql command to join across the tables?

3 solutions

Solution 1

Something like this?

var items =
    (from a in _tableOne
    join b in _tableTwo on a.ID equals b.A_ID
    join c in _tableThree on a.ID equals c.A_ID
    join d in _tableFour on a.ID equals d.A_ID
    orderby a.ID descending
    select new { a.Name, b.B_Field, c.C_Field d.D_Field}).Take(30)

Posted 25-Apr-13 5:16am

WiganLatics

Comments

Sam Van den Bossche 25-Apr-13 16:33pm

yes that's a start.. now I have to order them on a date that is stored in each table separately.
I tried orderby b.date, c.date, d.date
but that's not the way to go

Solution 2

Ordering dates like that works for me.

What format are your dates in? You might get different results depending on that.

What order are your results in?

Posted 25-Apr-13 23:37pm

WiganLatics

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sam Van den Bossche · Accepted Answer · 2013-05-05T20:34:00

C#

var feedList =
                    ((from video in context.Video
                      orderby video.Date descending
                      select new { TypeID = video.VideoID, CodeID = video.CodeID, Date = video.Date }).Take(50))
                     .Union((from comment in context.Comment
                             orderby comment.Date descending
                             select new { TypeID = comment.CommentID, CodeID = comment.CodeID, Date = comment.Date }).Take(50))
                        .Union((from share in context.Share
                                orderby share.DateTime descending
                                select new { TypeID = share.ShareID, CodeID = (int?)share.CodeID, Date = share.DateTime }).Take(50))
                        .Union((from events in context.Event
                                orderby events.CreateDate
                                select new { TypeID = events.EventID, CodeID = events.CodeID, Date = events.CreateDate }).Take(50))
                               .ToList();



                foreach (var f in feedList)
                {
                    var feed = new FeedListItemsContract();
                    {
                        feed.TypeId = f.TypeID;
                        var blCode = new BLCode();
                        feed.CodeName = blCode.GetCodeNameById((int)f.CodeID);
                        feed.Date = f.Date;
                    }
                    result.Add(feed);
                }


                var resultSorted = result.OrderByDescending(s => s.Date).ToList();
                return resultSorted;

that's what I needed to do to make it work..
important is that every select returns the same ammount of tables!

thanks for the help!