I have been reading up a lot on LINQ since I have to write some code that does the equivalent of a LEFT OUTER JOIN in order to find data with NULL values on the right side of the join. I’m using the latest beta of Oxygene and the latest release of DA to do this.
Here is the DALINQ code that is ALMOST working.
var data := from invdet in fDataModule.DataAdapter.GetTable<lpv8oelib.Data.invhistd>
join ap in fDataModule.DataAdapter.GetTable<lpv8oelib.Data.apdata>
on
new class (F1:=invdet.MillerAd8,F2:=invdet.AdYear4) equals new class(F1:=ap.MillerAdNumber,F2:=ap.EntryYear)
into qres
where (invdet.InvoiceDate<=ReportEndDate) and
(invdet.InvoiceDate>=ReportStartDate)
from ap in qres.DefaultIfEmpty
select new class(AdNumber := invdet.MillerAd8,EntryYear := invdet.AdYear4,PubNumber := invdet.PubNumber,PubName := invdet.PubName,
InsertDAtes := invdet.InsertDates,ClientNum := invdet.ClientNum,ClientName := invdet.Clientname,InvoiceDate := invdet.InvoiceDate,
InvoiceNumber := invdet.InvoiceNumber,GrossAmount := invdet.GrossAmount,LineType := invdet.LineType,RecordType := invdet.RecordType,
CheckNumber := iif(ap=nil,String.Empty,ap.Checknumber));
The last field in the anon class is what can be null, so if there is NOT an apdata record to JOIN w/ the invhistd record, I want that field to be null/nil.
What am I doing wrong? When I compile this and run it, I get the following error:
Argument type ‘System.Void’ does not match the corresponding member type 'System.String’
which I believe correlates with the iif() function call.
Unfortunately both features (both JOIN on more than 1 condition and iif in the result set) aren’t supported atm. We’ll contact you via this thread when/if we’ll implement them.
workaround for now would be to add one more table to the server Schema that will use the desired SQL statement as a data source.
Thanks for the reply, even though its disappointing that this is not supported by DALINQ. I hope eventually it will be since DALINQ is such a nice feature.
Followup question - everything I’ve done with DA so far on the client side has utilized DALINQ, until now, obviously. Could you, or anyone, show me some simple DA code (in Oxygene) which does a join on multiple fields? Can I do this with DASQL? Do I need to use a different approach? Any suggestions would be appreciated.
I just revised my server to include a table that does the left outer join via SQL, and added in the required parameters. Now…how the hell do I call this with parameters from my client app???
Yes, DA SQL by itself supports JOIN clauses on multiple fields.
The parametrized table call code would look like
var par := new RemObjects.DataAbstract.Server.DataParameter(Name:= 'pEmpID', Value := 3);
var res := from x in LinqAdapter.GetTable<EmployeesParametrized>([par]) select x;
Thanks, Anton. Looks like I hve two approaches to choose from.
I did put a new table on my server via SM, and added in the query and required parameters. I followed your suggestions, and Carlo’s. Everything compiles fine but I get the following exception after my DALINQ code:
{“An exception occurred on the server: Field “AdYear4” not mapped to database field”}
As I said, I have the table/SQL entered in SM, and I did the ‘Update Fields’ keypress to build the fields based on the query. Why would I now be getting that exception?
Actually, I did get my query working via DA SQL and its running perfectly. I kind of like things better using either DALINQ or DASQL vs. hard coding in some queries on the server itself, so I’ll stick with this method for now until I run into another limitation in the future.
Thanks for your help, and if there is possibility of expanding the DALINQ capabilities that would be awesome to see.
var lResult := from c in self.LinqAdapter.GetTable<Clients>()
join o in self.LinqAdapter.GetTable<Jobs>()
on new class (A := c.CustomerIDField, B := c.CountryField) equals
new class (A := o.CustomerIDField, B := o.ShipCountryField)
select c;
is executed at the database level (this sample uses Firebird) as
SELECT
"t0"."Address", "t0"."City", "t0"."CompanyName", "t0"."ContactName", "t0"."ContactTitle",
"t0"."Country", "t0"."CustomerID", "t0"."Fax", "t0"."Phone", "t0"."PostalCode", "t0"."Region"
FROM
"Customers" "t0"
INNER JOIN "Orders" "t1" ON (("t0"."CustomerID" = "t1"."CustomerID") AND ("t0"."Country" = "t1"."ShipCountry"))
The DA LINQ query
var lQuery := from c in self.LinqAdapter.GetTable<Customers>()
join o in self.LinqAdapter.GetTable<Orders>()
on new class (A := c.CustomerID, B := c.PostalCode) equals
new class (A := o.CustomerID, B := o.ShipPostalCode) into ords
from o1 in ords.DefaultIfEmpty()
select new class(c.CustomerID, o1.OrderID);
is executed as
SELECT
"t0"."CustomerID", "t1"."OrderID"
FROM
"Customers" "t0"
LEFT OUTER JOIN "Orders" "t1"
ON (("t1"."CustomerID" = "t0"."CustomerID") AND
("t1"."ShipPostalCode" = "t0"."PostalCode"))
So as you can see both INNER and LEFT joins now can be performed over multiple columns.
Unfortunately conditional operator (IIF) won’t work if it references anything other than data table fields. Still as you can see this doesn’t affect possibility to execute LEFT JOIN queries.
Please contact us via support@ in case you need the updated pre-Beta build.
I’m resurrecting this thread roughly a year later to ask a followup question:
if I have your DALINQ left outer join query example:
var lQuery := from c in self.LinqAdapter.GetTable<Customers>()
join o in self.LinqAdapter.GetTable<Orders>()
on new class (A := c.CustomerID, B := c.PostalCode) equals
new class (A := o.CustomerID, B := o.ShipPostalCode) into ords
from o1 in ords.DefaultIfEmpty()
select new class(c.CustomerID, o1.OrderID);
How would I add on a 2nd JOIN clause to that for a three table join?
Here is the DALINQ code I have written that won’t compile. Compiler (Elements 8.3 latest beta) says that ‘crec’ is an unknown identifier.
var AHList := (from ahrec in fDataModule.DataAdapter.GetTable<adheader>
where AdNumYearList.Contains(ahrec.AdNumber+ahrec.EntryYear)
join atrec in fDataModule.DataAdapter.GetTable<adtext>
on new class (AdNumber := ahrec.AdNumber, EntryYear := ahrec.EntryYear) equals
new class (AdNumber := atrec.AdNumber, EntryYear := atrec.EntryYear) into AdsPlusText
from apt in AdsplusText.DefaultIfEmpty
join crec in fDataModule.DataAdapter.GetTable<client>
on ahrec.ClientNumber = crec.ClientNumber
select new class (AdNumber := ahrec.AdNumber,
EntryYear := ahrec.EntryYear,
ClientNumber := ahrec.ClientNumber,
ClientName := ahrec.ClientName,
adtext := atrec.AdText_Field,
InvoiceNumber := ahrec.InvoiceNumber,
Regarding := ahrec.Regarding,
SortKey := ahrec.SortKey)).ToList;
I’ve just created a new Oxygene Client + Server project and tried there a query
var data :=
from o in fDataModule.DataAdapter.GetTable<Orders>()
where o.CustomerId.Contains("A")
join od in fDataModule.DataAdapter.GetTable<OrderDetails>()
on new class (A1 := o.Id, A2 := o.CustomerId) equals new class (A1 := od.OrderId, A2 := od.SupplierId)
into baseValueA
from odd in baseValueA.DefaultIfEmpty()
join cust in fDataModule.DataAdapter.GetTable<Customers>()
on odd.SupplierId equals cust.Id
into baseValueB
from odc in baseValueB.DefaultIfEmpty()
select new class (o.Id, odd.SupplierId, odc.Name);
that was sent to the underlying DB as
SELECT "t0"."Id", "t1"."SupplierId", "t2"."Name"
FROM "Orders" "t0"
LEFT OUTER JOIN
(SELECT "t3"."OrderId", "t3"."SupplierId", "t3"."SupplierId" AS "SupplierId1" FROM "OrderDetails" "t3") "t1"
ON (("t1"."OrderId" = "t0"."Id") AND ("t1"."SupplierId1" = "t0"."CustomerId"))
LEFT OUTER JOIN "Customers" "t2"
ON ("t2"."Id" = "t1"."SupplierId")
WHERE ("t0"."CustomerId" LIKE @p0)