Invalid use of subquery

Dec 2, 2010 at 9:03 PM
The following query..

var
 employees = context.hr180.Where(p => p.Hr180CidPr155Acid.Count > 0).ToList();

returns an error "Invalid use of subquery".. The actual query that is generated is

SELECT t0.cactive, t0.cactivebp, t0.cactivepe, t0.cactivepr, t0.caddress1, t0.cid ;
FROM hr180 AS t0 ;
WHERE (( ;
  SELECT COUNT(*) ;
  FROM pr155a AS t1 ;
  WHERE (t1.cid = t0.cid) ;
  ) > 0)

I'm using VFPOLEDB.dll v9 if that matters.. Wonder what I can do to fix this?
Coordinator
Dec 2, 2010 at 9:48 PM

I'll have to look into fixing that... but for a quick fix, you can change the statement as follows:

var employees = context.hr180.Where(p => p.Hr180CidPr155Acid.Any()).ToList();


 

Dec 2, 2010 at 9:53 PM

Based on my research, I'm not sure if there is a way to make that subquery work with VFP. There might just be some queries that aren't possible with LinqToVFP due to VFP's limitations. I tried working the query by hand to achieve the desired results and just couldn't come up with anything. One possible fix to that problem would be to a join on PR155... if no records are returned, that would achieve the desired result.

Dec 2, 2010 at 9:55 PM

BTW, I've got a few more bugs to fix and enhancements to make to the generator before I can move on.. will definitely let you know what I did so that maybe they can be implemented in the official version.

Coordinator
Dec 8, 2010 at 9:10 PM

I posted an update to correct this issue.

Using the northwind tests as an example, here is how the select statement was re-written.

Original (invalid) Statement:

SELECT t0.City, t0.CompanyName, t0.ContactName, t0.Country, t0.CustomerID, t0.Phone ;
    FROM Customers AS t0 ;
    WHERE (( ;
      SELECT COUNT(*) ;
      FROM Orders AS t1 ;
      WHERE (t1.CustomerID = t0.CustomerID) ;
      ) > 0)

 

New Statement:

SELECT t0.City, t0.CompanyName, t0.ContactName, t0.Country, t0.CustomerID, t0.Phone ;
    FROM Customers AS t0 ;
    INNER JOIN ( ;
      SELECT ( ;
        SELECT COUNT(*) ;
            FROM Orders AS t2 ;
            WHERE (t2.CustomerID = t1.CustomerID) ;
        ) AS CountValue, t1.CustomerID ;
      FROM Customers AS t1) AS t1 ON (t1.CustomerID = t0.CustomerID) ;
    WHERE (t1.CountValue > 0) 

Dec 9, 2010 at 4:15 PM

Awesome, thanks! I really need to compile a list of problems I found and figure out if they can be addressed in an official release. Off the top of my head:

  • I had to rip out pluralization and singularization code, as that was causing compilation issues when I ran the generator against my database tables (hundreds of tables, some of those tables having hundreds of fields). For example, our database has a table called ar001 and a table called ar001s. Reproduce that scenario on your machine and I think you'll see the problem.
  • Some of the fields in our database have names that are reserved words in .NET (class, private, date, etc...). Not sure how to get around those problems. I just unchecked them from being generated for now, but I'd like to prevent from having to do that.
  • Seeing as how I have hundreds of tables, bazillions of fields, and a cornucopia of relationships on each table, I modified the code to automatically select all tables, all fields, and all relationships. I also wrote code to automatically name the relationships.
  • Having run through this process several times, it became immediately obvious that the generator needs some way to save and load the specified settings.
  • A minor priority here, but it would be nice if I could automate the code generation with a batch file... this would require the generator to support command line arguments.

Thoughts? I might be able to send you a generic copy of one of our databases (probably a couple hundred MB in size) if you wanted to see what I was talking about.