Help with Subsonic3 ActiveRecord LINQ query

Question

I have the following subsonic entities

TInvoiceHeader
TAccountAssociation

How can I achieve the following in LINQ (subsonic)

SELECT * from TInvoiceHeader
WHERE custid IN 
  (SELECT custid FROM TAccountAssociation
     WHERE username = 'a')

I need to bind the results to a GridView.

Update: I tried

Dim accounts As List(Of TAccountAssociation) = _
TAccountAssociation.Find(Function(x) x.UserName = "a")

        GridView1.DataSource = TInvoiceHeader.All() _
             .Where(Function(x) accounts.Contains(x.custID))
        GridView1.DataBind() 

But I get an error "...nested function dows not have the same signature as delegate"

Update:

I really dont freaking get this...

why does this work

        Dim accounts() As String = {"N12345", "A12455"}


        GridView1.DataSource = TInvoiceHeader.All(). _
Where(Function(c) accounts.Contains(c.custID))
        GridView1.DataBind()

But this doesn't

Dim accounts  = TAccountAssociation.Find(Function(x) x.UserName = "a")

        GridView1.DataSource = TInvoiceHeader.All(). _
Where(Function(c) accounts.Contains(c.custID))
        GridView1.DataBind()

Update

I ended up using the Fluent Query

GridView1.DataSource = New customerWEBDB().Select.From(Of TInvoiceHeader)_
              .Where("custID") _
              .In(New customerWEBDB().SelectColumns("custID") _
              .From(Of TAccountAssociation) _
              .Where("UserName").IsEqualTo("aaa")) _
              .ExecuteTypedList(Of TInvoiceHeader)()

 GridView1.DataBind()

Hopefully someone will show me something better.

Answer 1

Have a look at this 101 linq examples. There is some great stuff here. Also read through Scott Gu's blog - the examples are with Linq to SQL but the LINQ stuff should be very similar.

You could do something like this:

var query = (from IH in db.TInvoiceHeader
              join AA in db.TAccountAssociation on 
               IH.custid equals AA.custid
              where aa.username.equals("a")
              select ID).ToList();

This will work as long as long as ID.custid and aa.custid are the same type (and are both nullable or non nullable). If this is not the case, you'll need something like this:

var query = (from IH in db.TInvoiceHeader
                  join AA in db.TAccountAssociation on 
                  new { ID = IH.custid.Value } equals new {ID = AA.custid}
                  where aa.username.equals("a")
                  select ID).ToList();

IH.custid.Value would be used if IH.custid is a nullable type

You can now bind query directly to a gridview.

I have not tested this code - and there are several other ways to accomplish your goal.

Good luck,

Patrick

Answer 2

There is a good answer to subquery's in linq here that will help:

http://stackoverflow.com/questions/418609/how-to-do-subquery-in-linq

i have never had to do subqueries with subsonic 3 however i would suggest that anything advanced SQl wise is better to do as a view or a stored proc so that you don't come up against any missing parts of the linq query builder in subsonic (last time i checked there were a few things it didn't fully do yet)

Answer 3

The answer to your question (but in C#), and using In rather than the join, would be:

var q = Db.Select.From<TInvoiceHeader>()
    .Where(TInvoiceHeaderTable.custidColumn)
    .In(Db.SelectColumns(TAccountAssociationTable.custidColumn)
             .From<TAccountAssociation>()
             .Where(TAccountAssociationTable.usernameColumn)
             .IsEqualTo("a")
    );

List<TInvoiceHeader> collection = q.ExecuteTypedList<TInvoiceHeader>();

I have a similar question on SO regarding NotIn: http://stackoverflow.com/questions/2995268/subsonic-3-activerecord-nested-select-for-notin-bug