Linq query Join objects with different data sources?

Question

I am trying to join two objects, the first is a (static) local object defined in a Helper and the second (Subsonic) Database object.

Here is the offending extract from my repository, I wont bore you with the models and helpers unless requested.

public IQueryable GetData(string DataType) {

        IQueryable<DatabaseObject> datalist = (
            from t in db.All<DatabaseObject>()
            join e in WebHelpers.LocalList.AsQueryable<LocalObject>()
            on t.Type equals e.Type
            orderby t.DateOccurred descending
            select t
        ).Where(e => e.Category == TransType);

        return datalist;
    }

I realise that I could me my life 1000 times easier by putting this table into the database, and for the next release I may very well do this. But is there a way to achieve what I am trying to do? Im thinking this is either (a) Im not returning the correct datatype as the view model expects IQueryable or (b) Subsonic is causing the issue.

Answer 1

I am afraid SubSonic doesn’t support this kind of cross-referencing (at least it didn’t last time I tried).

You can work around this by using SubSonic only to retrieve the necessary data from the database, but then do the Join in normal C# Linq-to-Objects:

public IQueryable GetData(string DataType)
{
    // Get a list of the types we need
    var requiredTypes = WebHelpers.LocalList.Select(l => l.Type)
                                  .Distinct().ToArray();

    // Retrieve all the relevant rows from the database
    var dbData = db.All<DatabaseObject>()
                                  .Where(d => requiredTypes.Contains(d.Type))
                                  .ToArray();

    // Do the join locally
    return (
        from t in dbData
        join e in WebHelpers.LocalList
        on t.Type equals e.Type
        orderby t.DateOccurred descending
        select t
    ).Where(e => e.Category == TransType);
}

(By the way, you can probably achieve better performance if you move the .Where() to above the orderby because then there is less to sort. Perhaps you can even include it in the database query (the one that retrieves dbData). I can’t quite tell because your code is confusing because it references a TransType which you haven’t declared anywhere, and the method has a parameter DataType which is not used anywhere, and you use t and e inconsistently.)

Answer 2

My final solution was to reorganise the where clauses to ensure that the select returns only the correct datatype (IQueryable). Without reorganising the where clauses, the join data is still passed through the object despite specifying select t.

public IQueryable GetData(string DataType)
{

     IQueryable<DatabaseObject> dbData = (
        from t in db.All<DatabaseObject>().Where(e => e.Category == TransType)
        join e in WebHelpers.LocalList
        on t.Type equals e.Type
        orderby t.DateOccurred descending
        select t
    ); 

    return dbData;
}