Solved

Linq - What is the best way to get the latest record in the Reports joined to the record in the SiteList table?

Posted on 2014-04-02
12
500 Views
Last Modified: 2016-02-16
4-2-2014-3-24-32-PM.png
What I am trying to achieve is this. Each record in the "SiteList" table joined with the most current record by "ReportCreateDate" in the Reports table.  This should be straight forward but I keep drawing a blank. How do I do this?
0
Comment
Question by:Todd710
  • 6
  • 5
12 Comments
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 100 total points
ID: 39973459
select sl.siteid,sl.sitename,max(rr.reportcreatedate) from sitelist sl, reports rr
where sl.siteid=rr.siteid
group by sl.siteid,sl.sitename

if you want to select more columns then you have to add the same in group by clause
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 400 total points
ID: 39974083
Hi Todd710;

Try something like this query to see if it gives the results you are looking for.

var results = from slist in SiteList.AsEnumerable()
              join report in Reports.AsEnumerable() on slist.Field<int>("SiteID") equals report.Field<int>("SiteID")
              group new { slist, report } by slist.Field<int>("SiteID") into reportGroup
              select new
              {
                  SiteID = reportGroup.Key,
                  Report = reportGroup.OrderByDescending(r => r.report.Field<DateTime>("ReportCreateDate")).FirstOrDefault( )
              };

In the results you will have one of these objects for every recort in the SiteList table.
SiteID  Report
        An Anonymous type with two object slist and report              
1       SiteID 1 
        SiteName Albany 
        MiscData New York 
 
        report 5DataRow4 
        ReportID 9 
        SiteID 1 
        ReportCreateDate 1/5/2014 10:01:03 AM 
        ReportData alpha 
 

Open in new window

0
 

Author Comment

by:Todd710
ID: 39978137
@ wasimibm - This is the right SQL query but it was looking for Linq.
@Fernando Soto - Your Linq query does not work.  Here is what I did to be able to use it in a test enviroment.
try
{
//fDataModule.DataAdapter.GetTable<SITELIST>() produces Enumerable table data
//This shows the count correctly
var count = (from slist in fDataModule.DataAdapter.GetTable<SITELIST>() select slist).Count();

//This produces no records
var results = (from slist in fDataModule.DataAdapter.GetTable<SITELIST>()
	//fDataModule.DataAdapter.GetTable<REPORTS>() produces Enumerable table data
   join report in fDataModule.DataAdapter.GetTable<REPORTS>() on slist.SITEID equals report.SITEID
   group new { slist, report } by slist.SITEID into reportGroup
   select new
   {
	   SiteID = reportGroup.Key,
	   Report = reportGroup.OrderByDescending(r => r.report.REPORTCREATEDATE).FirstOrDefault()
   });
}
catch(Exception ex)
{
	MessageBox.Show("Error: " + ex.StackTrace);
}

Open in new window

I appreciate both of your help! Unfortunately I am still unable to resolve this.  I am still working on this so if you have any additional ideas I would greatly appreciate it.  I am going to focus on converting wasimibm's query to Linq.

Thanks!
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39978187
@Todd710, seeming that the question was vague on the technology that you are using I made an assumption that you was using a DataTable object and using Linq to DataSet and not using a database provider such as Linq to SQL, Linq to Entity Framework, Linq to Oracle or some other provider. The only difference between the query I posted and the one that you modified is the data provider, in other words how you access the data provider to get to the underlying properties. In order to have members at EE to provide code that you can just drop in to your program you need to supply all the pertinent information otherwise we can only assume what the question is asking for.
0
 

Author Comment

by:Todd710
ID: 39978302
@Fernando Soto - My apologies this was not meant to attack your knowledge on the subject.  I was trying to simplify the case.  I will be more careful in my wording in the future.  I am just trying to get to the bottom of this in the simplest possible way.  This is my mistake I assumed that that enumerable data in the Linq query would work the same way.  Again no offence intended.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39978360
No offense was taken I just want to state that not enough information was given. And yes the basic operations of Linq are the same in most dialects of linq there are many differences in them as well so it does help to know what the technology it is being used in.

Now in your modified version of the query, what is it not doing that you want it to do? Are there any exceptions and if so what are the exception and inner exception messages that were given.
0
 

Author Comment

by:Todd710
ID: 39978620
This is a sample I set up to illustrate a problem I have in a different scenario I am not able to give you directly.  The technology in play here is:

Database = Firebird
Linq Tooling =  RemObject DALinq
IDE = VS2012 Pro

I am not receiving any exceptions from the try catch just no records.  However if I try the see the data that is return I get this:
VS-imageI have tried modifing the group statement like this:
group new { sl, r } by new { sl.SITEID, r.REPORTCREATEDATE, r.REPORTDATA } into rg

Open in new window

and leaving the "OrderByDescending" out I get all the records in the Records table this is what I have now:
var results =
	(from sl in fDataModule.DataAdapter.GetTable<SITELIST>()
	 join r in fDataModule.DataAdapter.GetTable<REPORTS>()
			 on sl.SITEID equals r.SITEID
	 group new { sl, r } by new { sl.SITEID, r.REPORTCREATEDATE, r.REPORTDATA } into rg
	 select new
	 {
		 siteid = rg.Key.SITEID,
		 rdata = rg.Key.REPORTDATA,
		 //rdate = rg.OrderByDescending(x => x.r.REPORTCREATEDATE).FirstOrDefault()
		 rdate = rg.Key.REPORTCREATEDATE
	 })

Open in new window

I need it to end up like this:
return dataHopefully I have not made matters worse.  Thanks!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39980561
Is it possible that the column REPORTCREATEDATE can be null in the database?
0
 

Author Comment

by:Todd710
ID: 39980574
No.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39980578
Then I am at a lost why the OrderByDescending is not working. The exception that you are getting leads me to believe that but if you say it is not then I do not know why it is happening.
0
 

Author Comment

by:Todd710
ID: 39983274
I am starting to believe that this might be a bug in the RemObject DALinq controls.  I have sent an email to their support to see what they can produce.  I will update the question when I have the results of that correspondence.   Thanks for the help!
0
 

Author Closing Comment

by:Todd710
ID: 39999219
@ wasimibm - Thanks for the correct SQL it was useful.
@Fernando Soto - Your answer was correct for standard Enumerable data.  However it appears that I am not working with standard data.  I am working with the RemObject DALinq  vendor for a solution.

Thanks so much for your help!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question