[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
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
Medium Priority
?
535 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 300 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 64

Accepted Solution

by:
Fernando Soto earned 1200 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 64

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 64

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 64

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 64

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

872 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