How to do joins without join in C#/Linq?

Rhywden

Yeah, sounds silly, right?

Here's the gist: I'm trying to develop a small C# Modern app which uses the Azure Mobile Service for syncing between devices. Due to the fact that I also want the app to work offline, I employ the offered solution of using SQlite as a backend on the client. This is all done pretty automagically by defining a data model for the tables I want.

Now, however, I also want a many-to-many relationship between two data models. And the interface provided to access the tables doesn't provide Linq Joins: http://msdn.microsoft.com/en-us/library/azure/dd135725.aspx

So, currently I'm a bit stumped as to in which direction to look. Do I try to query the underlying tables directly? Do I try to recreate a join only with foreach and .where and thus use brute force? Or is there something else I can try?

Code looks roughly like this for a single data model.

Initialization upon page load executes this:

protected override async void OnNavigatedTo(NavigationEventArgs e)
{
    if (!App.MobileService.SyncContext.IsInitialized)
    {
        var store = new MobileServiceSQLiteStore("localsync.db");
        store.DefineTable<TodoItem>();
        await App.MobileService.SyncContext.InitializeAsync(store);
    }
    await RefreshItems();
}

private async Task RefreshItems()
{
    items = await todoTable
                  .Where(todoItem => todoItem.Complete == false)
                  .ToCollectionAsync();
    ListItems.ItemsSource = items;
}

A sample data model would look like this:

public class TodoItem
{
    public string Id { get; set; }

    [JsonProperty(PropertyName = "text")]
    public string Text { get; set; }

    [JsonProperty(PropertyName = "complete")]
    public bool Complete { get; set; }

    [JsonProperty(PropertyName = "test")]
    public string Test { get; set; }

    [Version]
    public string Version { get; set; }
}

Data fields supported for syncing are string, int, bool and Date. Ids are strings containing a GUID. A sync executes a push first, then pulls all changes. After a successful sync I have to call RefreshItems() again.

The rows in each table will probably be in the hundreds, maybe a thousand entries.

RaceProUK

I'd look into querying the tables directly, calling ToList() on the resulting IEnumerables to pull them out of the data provider, and then doing the LINQ joins on the lists, as you'll be in LINQ to Objects then, which does support joins. The issue though is performance, so be sure to benchmark it.

accalia

well you could do:

var tbl1 = (for x in table1 where x.name = 'foo' select x).ToList();
var tbl2 = (for x in table2 where x.name = 'foo' select x).ToList();
var result = from x in tbl1 join y in tbl2 on x.id = y.fid select new { table1 = x, table2 = y };

it would possibly pull data that would be excluded on the join, but depending on your where clauses and domain logic that might not be an issue.

EDIT: hanzo'd because i took the time to write an example....

Rhywden

I'm not quite sure, though, how to execute an SQL query directly on the MobileServiceSQLiteStore("localsync.db")

The documentation is a bit sparse in that regard.

RaceProUK

I wasn't talking about executing a SQL query directly

@accalia's example is what I had in mind ;)

Rhywden

Yeah, saw that. Will try that solution - the data sets won't be too large so it shouldn't be too much of an issue.

The answer from the Dev team for that issue (because I'm not the only one with that problem) was:

"Create a view on the server side!" - "But I'm using offline as well and thus need joins on the client!" - "Well, we don't plan on adding them."

Jaime

In this situation, I usually build a GetByIDs() method on one of the entities and do something like:

var parents = Parent.GetBySomething(something);
var children = Child.GetByIDs((from p in parents select p.KeyField));
return (
        from p in parents
        select new {
                     Foo = p.Foo,
                     Bar = (from c in children where c.KeyField == p.KeyField).First().Bar
                   }).ToList();

@Rhywden said:

"Create a view on the server side!" - "But I'm using offline as well and thus need joins on the client!" - "Well, we don't plan on adding them."

Whether you build something on the server side or not, you still need a solution in the code since you are joining client data to server data. Whatever they do on the server will be done in addition to actually solving the problem.

Ebsan Uddin

@Jaime I know this is a 2 year old topic but I think it's still relevant. Have you come up with any other method to do this?

masonwheeler

Looking at the documentation, and at what's supported and what isn't, I get the distinct impression that this is not a SQL database on the backend, but rather a NoSQL database. Otherwise it should support joins.

It's hard to be sure, though.

xaade

@Rhywden said in How to do joins without join in C#/Linq?:

"Well, we don't plan on adding them."

Rhywden

@masonwheeler said in How to do joins without join in C#/Linq?:

Looking at the documentation, and at what's supported and what isn't, I get the distinct impression that this is not a SQL database on the backend, but rather a NoSQL database. Otherwise it should support joins.

It's hard to be sure, though.

Naw, the lack of support for JOINs rather stems from the fact that the basic functionality is data synchronization through a REST endpoint. Offline capabilities (and subsequent syncs with versioning) are bolted on top of that. And since it's still REST-based, creating a JOIN query would be a non-trivial endeavour.

@Ebsan-Uddin said in How to do joins without join in C#/Linq?:

@Jaime I know this is a 2 year old topic but I think it's still relevant. Have you come up with any other method to do this?

Yes, I used a brute-force approach and simply casted the results from the queries (which are IEnumerable<> if I recall correctly) to a proper IList<> and then did a LINQ join using those IList<>s on the client. Which of course means that I'm holding a bit more data than I need in memory - but the individual datapoints are small so it shouldn't be a problem, even with a thousand query results.

Weng

@Rhywden said in How to do joins without join in C#/Linq?:

And since it's still REST-based, creating a JOIN query would be a non-trivial endeavour.

And we live in a world where design buzzwords dictate features!