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
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
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
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);
Thanks,
Zach