Join tables - Could not infer relationship

ServiceStack Auto Query
1

I have only used AutoQuery with single tables but my customer needs to be able to query against related fields.

CREATE TABLE [dbo].[tbl_Issues](
[id] [int] IDENTITY(1,1) NOT NULL,
[coc] nvarchar NULL,
[issue] nvarchar NULL,
[communication] [int] NULL,
[date_added] datetime2 NOT NULL,
[collectionSiteid] [int] NOT NULL)

CREATE TABLE [dbo].[tbl_COLLECTION_SITES](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[COLLECTION_SITE] varchar NULL,
[ADDRESS] varchar NULL, …)

I had:

public class FindIssue : QueryDb

and that is working well for querying tbl_Issues.

Modified to:

public class FindIssue : QueryDb, IJoin<Issue,CollectionSite>

and now:

AutoQuery.CreateQuery(query, Request, db)

is throwing Exception:

“Could not infer relationship between tbl_Issues and tbl_Collection_Sites”

How can I define the relationship?

2

You wont be able to use declarative joins, you’d need to use a Custom AutoQuery implementation, e.g:

public object Any(MyRequest query)
{
    using var db = AutoQuery.GetDb(query, base.Request);
    var q = AutoQuery.CreateQuery(query, base.Request, db);

    // Add explicit join
    q.Join<TableA,TableB>((a,b) => a.Field == b.Field);

    return AutoQuery.Execute(query, q, base.Request, db);
}
3

That fixed the exception. However, when I specify a joined coumn name on the querystring, no Where clause is created for it.

https://localhost:5005/json/reply/FindIssue?city=Orlando

“city” is in tbl_Collection_Sites

4

AutoQuery should populate the SqlExpression<T> for the main table, but you’d need to add any filters to the joined tables it doesn’t know about.