Simple.Data join without where clause including any primary table columns

Question

I am using Simple.Data and have been trying to find an example that will let me do a join with the only condition in the WHERE clause be from the joined table. All of the examples I have seen always have at least one column in the primary table included in the WHERE. Take for example the following data:

private void TestSetup()
{
    var adapter = new InMemoryAdapter();
    adapter.SetKeyColumn("Events", "Id");
    adapter.SetAutoIncrementColumn("Events", "Id");
    adapter.SetKeyColumn("Doors", "Id");
    adapter.SetAutoIncrementColumn("Doors", "Id");
    adapter.Join.Master("Events", "Id").Detail("Doors", "EventId");
    Database.UseMockAdapter(adapter);
    db.Events.Insert(Id: 1, Code: "CodeMash2013", Name: "CodeMash 2013");
    db.Events.Insert(Id: 2, Code: "SomewhereElse", Name: "Some Other Conf");
    db.Doors.Insert(Id: 1, Code: "F7E08AC9-5E75-417D-A7AA-60E88B5B99AD", EventID: 1);
    db.Doors.Insert(Id: 2, Code: "0631C802-2748-4C63-A6D9-CE8C803002EB", EventID: 1);
    db.Doors.Insert(Id: 3, Code: "281ED88F-677D-49B9-84FA-4FAE022BBC73", EventID: 1);
    db.Doors.Insert(Id: 4, Code: "9DF7E964-1ECE-42E3-8211-1F2BF7054A0D", EventID: 2);
    db.Doors.Insert(Id: 5, Code: "9418123D-312A-4E8C-8807-59F0A63F43B9", EventID: 2);
}

I am trying to figure out the syntax I need to use in Simple.Data to get something similar to this T-SQL:

SELECT d.Code FROM Doors AS d INNER JOIN Events AS e ON d.EventID = e.Id WHERE e.Code = @EventCode

The final result should be only the three Door rows for EventId 1 when I pass in an event code of "CodeMash2013". Thanks!

score 3 · Accepted Answer · answered Dec 04 '12 at 15:26

First, a general point: since you've got criteria against the joined Events table, the LEFT OUTER is redundant; only rows with matching Event Codes will be returned, which implies only those rows where the join from Doors to Events was successful.

If you've got referential integrity set up in your database, with a foreign key relationship from Doors to Events, then Simple.Data can handle joins automatically. With that in mind, this code will work, both with the InMemoryAdapter and SQL Server:

List<dynamic> actual = db.Doors.FindAll(db.Doors.Events.Code == "CodeMash2013")
               .Select(db.Doors.Id, db.Events.Name)
               .ToList();

Assert.AreEqual(3, actual.Count);

If you don't have referential integrity set up then you should, but if you can't for some reason, then the following will work with SQL Server, but will trigger a bug in the InMemoryAdapter that I've just fixed but haven't done a release for yet:

dynamic eventAlias;
List<dynamic> actual = db.Doors.All()
              .Join(db.Events, out eventAlias)
              .On(db.Doors.EventID == eventAlias.Id)
              .Select(db.Doors.Id, db.Events.Name)
              .Where(eventAlias.Code == eventCode)
              .ToList();

Assert.AreEqual(3, actual.Count);

I do have referential integrity set up on both the actual database and my in memory test data. Your first answer gives me exactly the results I wanted. Thanks! I also edited the question since I really wanted an INNER JOIN anyhow, not an OUTER. — Joe Kuemerle, Dec 04 '12 at 17:40
Hey Mark, i'm wondering if the bug that you described here related to the in-memory adapter still exists. I recently tried a very similar query to the second of your examples above (without referential integrity). I am not getting an exception, however the query isn't returning any results. I did notice that when I removed the where clause, the query works as expected. — Matt Wolin, Jul 10 '13 at 20:21

Dan Maharry · Answer 2 · 2012-12-04T12:45:32.980

0

UPDATE : This answer applies when using the Simple.Data SQL Server Provider, not the InMemoryAdapter

You could probably use the following for this:

db.Doors.All()
  .Select(
    db.Doors.Code)
  .LeftJoin(db.Events).On(db.Doors.EventID == db.Events.Id)
  .Where(db.Events.Code == eventCode);

You might need to experiment between using LeftJoin and OuterJoin depending on your provider. If you're using the ADO provider for example, the two functions both generate LEFT JOIN statements as LEFT JOIN and LEFT OUTER JOIN are synonymous in t-sql.

If you need to use aliases for some reason, the syntax is slightly different.

dynamic EventAlias;
db.Doors.All()
  .LeftJoin(db.Events.As"e", out EventAlias).On(db.Doors.EventID == db.EventAlias.Id)
  .Select(
    db.Doors.Code)
  .Where(db.EventAlias.Code == eventCode);

There's no reason why a where clause must contain a field from the primary key table. You can find more examples of Joins here on the Simple.Data doc site. Click Explicit Joins when you get there.

edited Dec 04 '12 at 12:45

answered Nov 30 '12 at 22:29

Dan Maharry

1,499
2
19
35

Neither of those queries is returning any results when I run it against my in memory test data (or against a SQL Server test DB) using Simple.Data version 1.0.0-rc3. I can't get tracing to work correctly and show me the query it is executing so I can't troubleshoot it any further at the moment. I am going to keep trying to get a trace of the actual query being executed. – Joe Kuemerle Dec 03 '12 at 14:51
Joe, Tracing won't work because the InMemoryAdapter is in memory. It's part of the Simple.Data.Core package and doesn't touch any SQL at all - you'd have to create the database in SQL Server and point Simple.Data at that to check the SQL it is creating. Simple.Data would use the SQl Server provider at that point which creates the SQL commands sent to a database. – Dan Maharry Dec 04 '12 at 12:36

Simple.Data join without where clause including any primary table columns

2 Answers2