Sage Developers' Blog

Sometimes it can be useful to create lists that contain content that is fixed by you in the query and isn’t fetched from a data source.

A simple example of this might be creating a set of links to web-based content.  The links are fixed, but the content may change.  In the following example, the list data is first written  into a string array and then ‘Selected’ and cast to an IQueryable collection.  The only slight subtlety with this is the necessity to put this into the Queryable form and to ensure that fields are labelled and that the system can infer their type using the Select statement:

string[] Descriptions = new string[3];
//
Descriptions[0]="What is a workspace?|Bp5nPGr0ptw";
Descriptions[1]="How To Build A Sage 200 Workspace - Part 1|1wBcLZkBlxM";
Descriptions[2]="How To Build A Sage 200 Workspace - Part 2|98I7REkQTGA";
//
Descriptions.Select
(
    s=>new
    {
        Description=s.Split('|')[0],
        URL="http://www.youtube.com/v/" + s.Split('|')[1] + "&hl=en_US&fs=1&"
    }
)
.Dump();

A more practical scenario might be one where you need to programatically develop some fields in a query, but retrieve other fields from a data source.  In the following LINQPad snippet (for example) we compare daily sales for two calendar years.  The Sage 200 data only has information about days where we actually made sales.  If we want to compare these two series against an accurate timeline, then we need to generate our own list of days and join the series for the two years onto it:


int[] days = new int[365];
//
for(int i=0; i<365; i++){days[i]=i+1;}
//
var q1=days.Select
 (
  s=>new
  {
   Day=s
  }
 );
//
var q2=SOPOrderReturns
 .Select
 (
  s=>new
  {
   SaleDay=s.DocumentDate.Value.Day,
   Value2008=(s.DocumentDate.Value.Year==2008) ? s.TotalGrossValue :0,
   Value2007=(s.DocumentDate.Value.Year==2007) ? s.TotalGrossValue :0
  }
  )
 .GroupBy(g=>g.SaleDay)
 .Select
 (
  s=>new
  {
   SaleDay=s.Key,
   V2007=s.Sum(e=>e.Value2007),
   V2008=s.Sum(e=>e.Value2008)
  }
 );
//
var q3 =q1
  .GroupJoin
  (
   q2.DefaultIfEmpty(),
   l=>l.Day,
   r=>r.SaleDay,
   (l,r)=>new
   {
    l.Day,
    V2007=(r.Any()) ? r.First().V2007 : 0,
    V2008=(r.Any()) ? r.First().V2008 : 0
   }
  )
.Dump();