Simple.Data framework: join doesn't work

Question

I'm trying to do a simple join with Simple.Data but I can't get it working. I'm sure that I'm missing something but I don't get it...

SQL:

CREATE TABLE TestA
(
    ID INTEGER PRIMARY KEY,
    Txt TEXT
);

CREATE TABLE TestB
(
    ID INTEGER PRIMARY KEY,
    Aref INTEGER,
    Txt TEXT,

    FOREIGN KEY(Aref) REFERENCES TestA(ID)
);

INSERT INTO TestA
VALUES (1, 'This is Test A!');

INSERT INTO TestB
VALUES (1, 1, 'This is TestB!');

C#:

db.TestB.Find(db.TestB.Aref = db.TestA.ID);

If I run the code I'll get a RuntimeBinderException: 'Simple.Data.ObjectReference' does not contain a definition for 'Aref'.

I'm using Simple.Data.Core/Ado v0.12.2.2 and Simple.Data.Sqlite v0.12.2.4

I already checked the Docs but I can't see my mistake. What I am doing wrong?

Edit: I also tried the index style:

db["TestB"].Find(db["TestB"]["TestA"]["ID"] == db["TestB"]["Aref"]);

The generated SQL looks like this:

select [TestB].* from [TestB]  JOIN [TestA] ON ([TestA].[Aref] = [TestB].[ID]) where [TestA].[ID] = [TestB].[Aref]

It seems like that the tables in the ON part are inverted, actually it should be like the WHERE part ...

I think your operator is wrong. According to docs it should be `==` — MilkyWayJoe, Mar 12 '12 at 20:57
@MilkyWayJoe yes you're right. If I use the == operator I get a different exception, AdoAdapterException: "SQLite error no such column: TestA.ID". The generated SQL code: select [TestB].* from [TestB] where [TestB].[Aref] = [TestA].[ID] — MBulli, Mar 12 '12 at 21:09
Do you have a DB client in which you can run the generated select? If you don't, download and try to execute it there. If it works, then maybe it's a bug. Maybe you could try to update your models on your project as well (sometimes, on extreme cases, I delete my objects and create them again) — MilkyWayJoe, Mar 13 '12 at 05:09
It seems to be a problem with Simple.Data.Sqlite, I wrote a bug report back then it still isn't solved, but for now this problem doesn't affect me. https://github.com/NotMyself/Simple.Data.Sqlite/issues/5 — MBulli, Apr 10 '12 at 07:34

score 0 · Answer 1 · answered Dec 02 '14 at 10:15

      List<TestB> source = db.TestB.FindAll()
            .Select(db.TestB.ID,
            db.TestB.Aref,
            db.TestB.Txt,
            db.TestB.TestA.ID,
            db.TestB.TestA.Txt)
            .Join(db.TestA).On(db.TestA.ID == db.TestB.Aref);

This example get all record from table TestB. You can select what column you want get in section select. I hope this help you.

Jerry · Answer 2 · 2012-03-14T13:01:50.033

0

Try this:

using(var db = new MyEntities())
{
    var sql = db.TestB.Include("TestA");
    var data = sql.ToList();

    //the var data will now contain the queried data.
    //you could bind the var to a datagrid. 

    //WPF datagrid
    MydataGrid.DataContext = data;

    //Asp.net datagrid
    MyDatagrid.DataSource = data;
    MyDatagrid.DataBind();

    //Or loop thru the results
    foreach(var item in data)
    {
         MessageBox.Show("Column -> " + item.ColumnName);
    }


}

edited Mar 14 '12 at 13:01

answered Mar 12 '12 at 20:57

Jerry

6,357
8
35
50

This code returns a Simple.Data.FunctionReference object. How do I receive the data from this object? – MBulli Mar 12 '12 at 21:13
See my revised answer above. This assumes that you have created a EDMX and that the entities are associated. – Jerry Mar 14 '12 at 13:18

Simple.Data framework: join doesn't work

2 Answers2