Passing DataContext as Parameter

Passing DataContext as Parameter

Postby zsessary » Sat 07 May 2011 00:24

I have made some changes to an existing project by eliminating a wrapper class we had around our DataContext. In doing so I have noticed that when i pass the actual DataContext as a parameter to a function that is to return iQueryable(of T) I cannot use this from within a query as a left join.

An example is if i had a complex business rule regarding who was allowed to be an "Agent". In several locations (Web, WinForms app) i need to perform this query with additional criteria. This is how i tried to implement.

Code: Select all
    Public Shared Function GetEligableAgents(ByVal dc As Data.TestDataContext) As IQueryable(Of Data.Agent)
        Return dc.Agents.Where(Function(w) w.BadQuality Is Nothing)
    End Function


This method would be called by various applications like this:
Code: Select all
        Dim dc As New Data.TestDataContext()
        For Each record In From agent In GetEligableAgents(dc) _
                           Join name In dc.Names On agent.Id Equals name.AgentID _
                           Where name.LastName.Contains("Johnson")
            ' do stuff
        Next


So that's the concept. if i attempt to do a left outer join to my function, i get the exception "NotSupportedException" with a message = "GetEligableAgents".

below is a method i have within a console application that shows all the successful ways you can perform a query and the one way that seems to cause this exception. The truly strange part is how you can use this method be creating a variable for it first, or by passing a wrapper class instead of the actual data context. See code and SQL to generate the database schema the code utilizes below.

Code: Select all
    Private Sub TestMethodInQuery()
        Console.WriteLine()

        Dim db1 As New Data.TestDataContext()
        For Each record In From p In db1.Parenttables _
                           Join o In db1.Childothertables On p.Id Equals o.Parenttableid _
                           Where o.Mydata = "Other Value 1"
            Console.WriteLine(record.p.Mydata)
        Next

        Console.WriteLine()

        Dim db2 As New Data.TestDataContext()
        For Each record In From p In db2.Parenttables _
                           From o In db2.Childothertables.Where(Function(w) w.Parenttableid = p.Id).DefaultIfEmpty() _
                           Where o.Mydata = "Other Value 1"
            Console.WriteLine(record.p.Mydata)
        Next

        Console.WriteLine()

        Dim db3 As New Data.TestDataContext()
        Dim subsetOfTables = GetSubsetOfChildOtherTables(db3)
        For Each record In From p In db3.Parenttables _
                           Join o In subsetOfTables On p.Id Equals o.Parenttableid
            Console.WriteLine(record.p.Mydata)
        Next

        Console.WriteLine()

        Dim db4 As New Data.TestDataContext()
        For Each record In From p In db4.Parenttables _
                           From o In (db4.Childothertables.Where(Function(w) w.Mydata = "Other Value 1")).Where(Function(w) w.Parenttableid = p.Id).DefaultIfEmpty()
            Console.WriteLine(record.p.Mydata)
        Next

        Console.WriteLine()

        Dim wrapper As New DataContextWrapperClass With {.DataContext = New Data.TestDataContext}
        For Each record In From p In wrapper.DataContext.Parenttables _
                           From o In GetSubsetOfChildOtherTables(wrapper).Where(Function(w) w.Parenttableid = p.Id).DefaultIfEmpty()
            Console.WriteLine(record.p.Mydata)
        Next

        Console.WriteLine()

        Dim db5 As New Data.TestDataContext()
        For Each record In From p In db5.Parenttables _
                           From o In GetSubsetOfChildOtherTables(db5).Where(Function(w) w.Parenttableid = p.Id).DefaultIfEmpty()
            Console.WriteLine(record.p.Mydata)
        Next

    End Sub

    Private Class DataContextWrapperClass
        Public DataContext As Data.TestDataContext
    End Class

    Private Function GetSubsetOfChildOtherTables(ByVal db As Data.TestDataContext) As IQueryable(Of Data.Childothertable)
        ' this applies complex business rules to a query that can be used in various classes/projects
        Return db.Childothertables.Where(Function(w) w.Mydata = "Other Value 1")
    End Function

    Private Function GetSubsetOfChildOtherTables(ByVal db As DataContextWrapperClass) As IQueryable(Of Data.Childothertable)
        ' this applies complex business rules to a query that can be used in various classes/projects
        Return db.DataContext.Childothertables.Where(Function(w) w.Mydata = "Other Value 1")
    End Function


and the DB Schema...
Code: Select all
/*
-- Drop talbes and values
DROP TABLE PARENTTABLE CASCADE CONSTRAINTS PURGE;
DROP TABLE CHILDINSERTTABLE CASCADE CONSTRAINTS PURGE;
DROP TABLE CHILDOTHERTABLE CASCADE CONSTRAINTS PURGE;

-- Drop sequences
DROP SEQUENCE PARENTTABLE_SEQ;
DROP SEQUENCE CHILDINSERTTABLE_SEQ;
DROP SEQUENCE CHILDOTHERTABLE_SEQ;
*/

-- Create the Tables
CREATE TABLE parenttable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL
);

CREATE TABLE childinserttable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL,
parenttableid NUMBER NOT NULL,
CONSTRAINT parenttable_childinserttable
FOREIGN KEY (parenttableid)
REFERENCES parenttable (id)
);

CREATE TABLE childothertable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL,
parenttableid NUMBER NOT NULL,
CONSTRAINT parenttable_childothertable
FOREIGN KEY (parenttableid)
REFERENCES parenttable (id)
);

-- Create the Sequences
CREATE SEQUENCE PARENTTABLE_SEQ NOCACHE START WITH 2;
CREATE SEQUENCE CHILDINSERTTABLE_SEQ NOCACHE START WITH 2;
CREATE SEQUENCE CHILDOTHERTABLE_SEQ NOCACHE START WITH 4;

-- Create the Triggers
CREATE TRIGGER PARENTTABLE_BEFORE_INSERT
BEFORE INSERT ON PARENTTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT PARENTTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/

CREATE TRIGGER CHILDINSERTTABLE_BEFORE_INSERT
BEFORE INSERT ON CHILDINSERTTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT CHILDINSERTTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/

CREATE TRIGGER CHILDOTHERTABLE_BEFORE_INSERT
BEFORE INSERT ON CHILDOTHERTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT CHILDOTHERTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/

-- Insert initial Values
INSERT INTO PARENTTABLE VALUES (1, 'Parent Value 1');
INSERT INTO CHILDINSERTTABLE VALUES (1, 'Insert Value 1', 1);
INSERT INTO CHILDOTHERTABLE VALUES (1, 'Other Value 1', 1);
INSERT INTO CHILDOTHERTABLE VALUES (2, 'Other Value 2', 1);
INSERT INTO CHILDOTHERTABLE VALUES (3, 'Other Value 3', 1);


I hope this all makes sense It's nothing i can't work around, but it would be nice if this runtime error could be fixed.

Thanks,
Zach
zsessary
 
Posts: 10
Joined: Thu 25 Mar 2010 20:05

Postby StanislavK » Tue 10 May 2011 15:12

Apparently, the problem is that the runtime tries to translate this custom method to SQL. We will analyze the possibility of using custom methods that return Queryable in such situations, and will post here about the results.

As a workaround, you can, e.g., execute the method before building the query (as in the third example), or use the 'dot' notation, e.g.,
Code: Select all
For Each record In _
  db5.Parenttables.Join _
    (GetSubsetOfChildOtherTables(db5), Function(p) p.Id, _
    Function(o) o.Parenttableid, Function(p, o) New With {p, o})
  Console.WriteLine(record.p.Mydata)
Next
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby zsessary » Thu 12 May 2011 17:21

ok, thank you for the reply. this is basically what I had thought, but the inner workings are a bit beyond me so i wanted to verify.
zsessary
 
Posts: 10
Joined: Thu 25 Mar 2010 20:05


Return to dotConnect for Oracle