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
465 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
Comment Utility
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 62

Accepted Solution

by:
Fernando Soto earned 400 total points
Comment Utility
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
Comment Utility
@ 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
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
@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
Comment Utility
@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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Todd710
Comment Utility
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 62

Expert Comment

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

Author Comment

by:Todd710
Comment Utility
No.
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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
Comment Utility
@ 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
LINQ question 7 20
.net VBA word safemode 1 23
Managing SQL log files, SQL Server 2014 6 54
DataGridview Currency Formating? 22 38
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now