Linq to Entity - Workaround Subquery?

For quite sometime, I been trying to figure out how to run a subquery in a Linq to Entity format. I’m kinda old school on querying, I really like to write straight SQL, not linq stuff. And to be honest, I'm not really sure what the right terminology is for some of it....so this post may not be the best in the world.

I’m not really even sure if this is a 'linq subquery' or not, but it is a way of getting nested data. It works for me so that is good.

To give the background on the data being retrieved, I'm getting a User that may have many User_Roster rows tied to it, each User_Roster row has a Book row tied to it, each Book row may have a User_Test row tied to it. So in this example, I need to get down to a certain a User_Test...oh my, hope that makes sense.

//method that uses linq/entity to get data from the database
public static User Get(int userId)
{
   using (FakeEntities eContext = new FakeEntities ())
    {
     User user = eContext.User
     .Include("User_Roster.Book.User_Test")
      .Where(i => .ID.Equals(userId))
      .FirstOrDefault();
    return user;
   }
}

//sample method that would be a consumer of the previous linq query
public string GetUser(string request)
{

//setup some hard coded values for examples sake
  int bookId = 1234;


  //call the method that used linq to query the database
  User item = Get(userId);

  //subquery the list to the exact roster i need
  //on the second line: m is for User_Roster, any variable name works
  User_Roster ur = item.User_Roster
    .Where(m => m.Book.ID.Equals(bookId))
    .FirstOrDefault();

  //if i found the roster i need, get the data i'm looking for
  if (ur != null)
   {
    User_Test test = ur.Book.User_Test .FirstOrDefault();
     if (test != null)
        ....
     }
}

i know there is probably a much smarter way of doing this, but I've not come across any good examples on the internet. so if this post helps you, let me know!! i'm curious.

Active Reports - Fetch Data Fires Twice?

The past couple of days have been spent tracking down a problem I was having with a subreport showing blank data for the last record, even though it should have data. Whatever was the last record was, whether I had a single record or I had three records, it was all whitespace!!

Of course, I tore the report apart and nearly my hair out. I took out code that may have been tripping me up, I changed my data, I walked away from the computer. I read and re-read the Grape City support forum site. Nothing seemed to be working. But I did notice when I would debug, the FetchData would fire twice for the last record.

Nothing ever clicked with me until today. I was reading the support forum again and they warn of eArgs.EOF all over the place. (Scott, if you are reading this. I believe I saw a post from you on this!) And sure enough that was my problem. For this report, I needed to wrap the FetchData event around an eArgs.EOF check. And viola, I HAVE DATA.

private void report_FetchData(object sender, ActiveReport.FetchEventArgs eArgs)
{
if (eArgs.EOF == false)
{
...code here!
}
}

Whether this is right or wrong, it works, so I'm happy!!

Active Reports - Single report with multiple subreport

I have created a single Active Report with a single subreport before. Not too difficult. Mine looked like this in the code view:

//create an xml datasource for the subreport
DataDynamics.ActiveReports.DataSources.XMLDataSource xmlDS = new DataDynamics.ActiveReports.DataSources.XMLDataSource();

//load data
XmlDocument xDoc = new XmlDocument();
xDoc.LoadXml(booksXml.ToString());
xmlDS.FileURL = null;
xmlDS.RecordsetPattern = "//book";
xmlDS.LoadXML(xDoc.InnerXml);

//set report datasource
BookDetail ed = new BookDetail();
this.subReport1.Report = ed;
this.subReport1.Report.DataSource = xmlDS;

Okay, so I thought when I added a second subreport with different data, I could just do the same, but I added an if statement that skipped doing anything with the subreport if the object was null.

And, THAT was a problem, it seems. When I would run the report, nothing showed up in the second subreport, even though it should have. Even when I put a breakpoint in the subreport, the Fetch method wasn't being hit - that was a clue.

Well, I guess, even if you have null data you still gotta do something with the subsequent subreport. So, I found an example on the AR support site that explained this to me - setting up a dummy object.

//create an xml datasource for the subreport
DataDynamics.ActiveReports.DataSources.XMLDataSource finalDS = new DataDynamics.ActiveReports.DataSources.XMLDataSource();

XElement BookXml = new XElement("Books");
BookXml.Add(Book.ToXml());

//load data
XmlDocument doc = new XmlDocument();
doc.LoadXml(booksXml.ToString());
finalDS.FileURL = null;
finalDS.RecordsetPattern = "//book";
finalDS.LoadXML(doc.InnerXml);


User_Book book = Book.GetItem(b.ID, m.ID);

if book != null)
{
//set subreport datasource
BookDetail fed = new BookDetail();
this.subReport2.Report = fed;
this.subReport2.Report.DataSource = finalDS;
}
else
{
BookDetail fed = new BookDetail();
fed.DetailData = false;
this.subReport2.Report = fed;
}

Hope this helps someone!