I think I now understand your question and why you asked.

You want to control the list of Actors stored for each Movie by Populate().

The problem is that, for each new Movie, your ActorFilter method is called by Populate a number of times to create the list of Actors for the Movie.
 
ActorFilter needs to return a random Actor ID that is for a Man, and that was not already generated for this Movie.

Checking that the random Actor ID exists and is a Man is straightforward.

The trick is to check the interim list of Actors for this Movie to ensure that a duplicate ID is not returned.

Since Populate and ActorFilter are instance methods in Movie, the ..Actor syntax accesses the current/partial Actor %Collection.ListOfObj in the Movie currently being Populated.

Here are my example Movie and Actor classes to accomplish this.

Class Packet.Actor Extends (%Persistent, %Populate)
{
Property Name As %String [ Required ];

Index NameIndex On Name [ Unique ];

Property Age As %Integer(MAXVAL = 100, MINVAL = 10) [ Required ];

Property Sex As %String(DISPLAYLIST = ",Woman,Man", VALUELIST = ",F,M") [ Required ];
}

Class Packet.Movie Extends (%Persistent, %Populate)
{
Property Title As %String(POPORDER = 0) [ Required ];

Index TitleIndex On Title [ Unique ];

Property Date As %Date(POPORDER = 2) [ Required ];

Property Actor As list Of Packet.Actor(POPSPEC = ".ActorFilter()") [ Required ];

Method ActorFilter()
{
 // Returns a Packet.Actor ID, where Actor is a Man not already in this Movie's current Actors list
 // Assumes Actors was just populated, so <IdLocation>^Packet.ActorD</IdLocation> identifies location of last ID
 s maxID=^Packet.ActorD
 // loop until we find an ID or tried 100 times
 s tried=0
 s ID=""
 while ID="" {
    s tried=tried+1
    if tried>100 quit 
    s ID=$random(maxID)+1
    // ..Actor is this Movie's list of Actor IDs so far
    s found=0
    if ..Actor.Size>0 {
        //check whether this ID was already used
        s key=""
        s used=..Actor.GetObjectIdNext(.key)
        while key'="" {
            if ID=used {s found=1 quit } //this ID was already used
            s used=..Actor.GetObjectIdNext(.key)
        } 
    }
    if found s ID="" CONTINUE //already used
    s actor=##class(Packet.Actor).%OpenId(ID)
    if actor="" CONTINUE //not found
    if actor.Sex'="M" s ID="" CONTINUE //wrong Sex
 }
 quit ID
}

}

Sorry, but the problem sill is not clear to me.

First, I don't think that you want a SQL query that returns all movies with unique Man Actors.

I think you want to prevent save of a Movie if all Actors are not Man or list of Actors is not unique for this Movie.

Assuming that, are Movies being saved via SQL Insert/Update, via object Save, or both?

Once you confirm, we should have some suggestions to confirm that the Movie's list of Actors is acceptable.

I haven't tried this, but Microsoft's DbContext doc here:

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

seems to match our example down this page in our docs here:

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

I don't find ConfigureServices in our docs, but Microsoft indicates that it is optional:

https://docs.microsoft.com/en-us/aspnet/core/fundamentals/startup?view=a...

Sorry for lack of detail.

Roberto, Am I correct that you are having the common problem of SQL queries can return multiple columns that are too wide to display/print? A solution is to truncate the output widths without changing the class/table definition or the stored data. For example, if this is too wide: SELECT ID, Name, AltName, Address... FROM table WHERE BusinessType = ? there are solutions, depending on how/where the SQL is entered. If you are entering such queries manually, you can simply truncate, as with: SELECT ID, SUBSTR(Name,30), SUBSTR(AltName,25), Address... FROM table WHERE BusinessType = ? If this is a class query, you can change it to take these MAXLEN parameters. For example, in our SAMPLES namespace, class Sample.Person includes: Query ByName(name As %String = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_By_Name, SqlProc ] { SELECT ID, Name, DOB, SSN FROM Sample.Person WHERE (Name %STARTSWITH :name) ORDER BY Name } You could create a similar Query that accepts truncate values, like: Query ByNameT(name As %String = "", nameLen As %Integer = 50) As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_By_NameT, SqlProc ] { SELECT ID, SUBSTR(Name,1,:nameLen), DOB, SSN FROM Sample.Person WHERE (Name %STARTSWITH :name) ORDER BY Name } Then users can run such SQL queries like: SELECT * FROM Sample.SP_Sample_By_NameT('A',10) and see truncated column data. Dynamic SQL (%SQL.Statement) provides similar functionality.

Hello Vivek,

Microsoft LINQ adds query capability to .net.  You are asking whether similar things can be done with COS.

First, please confirm my understanding of your example.

Looking at our Sample.Person database, I see that property FavoriteColors is a list of strings. I can find or count all Persons with 'Red' as one of their FavoriteColors with SQL:
SELECT ID, FavoriteColors FROM Sample.Person WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE='Red')
SELECT COUNT(*)           FROM Sample.Person WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE='Red')

You can capture the count in COS code with:
 &sql(SELECT COUNT(*) INTO :myCount FROM Sample.Person WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE='Red'))

So COS provides the desired functionality for objects stored in the database.

 
You seem to want to examine a list of objects in memory to count those that have Property2='Red'

If your objects are in memory but not on disk, then I don't believe that COS offers a similar facility to LINQ WHERE.  Hopefully, iterating is not a major inconvenience.

Hello Edmund,

Which Caché version are you using?  I believe that some earlier versions were incomplete.  In particular, when you unzip/extract CacheEF.zip from folder <installdir>\dev\dotnet\bin\v4.0.30319
do you get 16 items as I do for Caché 2017.1.1.111?

Are Caché and Visual Studio on the same PC?

The instructions call for running setup with a one-time arg -installflags SetupVS.  ***Have other users of EF been able to run these?

What version of Visual Studio?  I have 2015 (14.0), and I found VsDevCmd.bat in C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\Tools.  But instead opened an admin cmd prompt to the indicated directory, and did:  C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE>.\devenv.com /setup
This seemed to take a minute or so.  I don't know if this is what finished the setup, but Visual Studio does now offer the Caché Server option.

On my Win10 64-bit...
Local Caché installations are registered here:
HKEY_CURRENT_USER\SOFTWARE\InterSystems\Cache\Configurations
HKEY_USERS\S-1-5-21-112145844-1872675854-1690816760-2965\SOFTWARE\InterSystems\Cache\Configurations

and servers here:
HKEY_CURRENT_USER\SOFTWARE\InterSystems\Cache\Servers
HKEY_USERS\S-1-5-21-112145844-1872675854-1690816760-2965\SOFTWARE\InterSystems\Cache\Servers

ODBC Drivers here:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI
ODBC DSNs here:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI

VS2013 data providers here:
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\VisualStudio\12.0\DataProviders
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VisualStudio\12.0_Config\DataProviders
HKEY_USERS\S-1-5-21-112145844-1872675854-1690816760-2965\SOFTWARE\Microsoft\VisualStudio\12.0_Config\DataProviders

VS2015 data providers here:
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VisualStudio\14.0_Config\DataProviders
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\VisualStudio\14.0\DataProviders
HKEY_USERS\S-1-5-21-112145844-1872675854-1690816760-2965\SOFTWARE\Microsoft\VisualStudio\14.0_Config\DataProviders

Do you have similar registry entries?

And, since these are registry changes, you likely need to restart Visual Studio, and perhaps Caché or Windows.
 

How about if you add a class method ICanSeeThisRecordNow() that returns TRUE or FALSE (based on your external criteria), and a calculated property/column ICanSeeIt that returns this value via SqlComputeCode. Then create a view as:

SELECT whateverColumns FROM myTable WHERE ICanSeeIt=TRUE

This still might relate to bad data.  You did not mention the Caché version you are running.

And you might try various simplifications of the failing query that eliminate rows, column and selection criteria to zero in on the offending data (if any).

If not successful, you might forward your cached query routine (assuming nothing confidential) to help with the analysis.

Another response with similar info...

So, your ClassMethod sample() is included in (or at least references) the class Eam.Empdetails, which, presumably, is as %Persistant class.  Therefore, the method %Save() adds the %New object you just defined as a record in the the database.

Your method returns a status from the %Save, and you should check that because, if it is not 1 ($$$OK), then there will be not data to read.

Assuming the record was successfully saved, it has an ID that you can use to read it back. You did not include your class definition, and so I don't know how the ID is specified.  The ID might be, for example, a system-generated sequential integer, the Name.id property you specified, or something else.  You might want to see what was saved by temporaliry adding the following line to the end of your method, compiling, and running it again.  It will display the contents of the object you just %Saved.  Near the top of the display is %%OID which is a Caché list consisting of the object's ID and the class to which it belongs.  You can retrieve this list with object method %Oid().  The ID is the first item in the list.  Alternatively, your ID might have been specified as Name.id.

To print out a record from the database, you would need to know its ID.  If you have a main program that calls your sample() method, you might want that method to also return the ID so the main program could read and display it.  One way to do this would be to add an output argument to sample(), changing its header to:
   ClassMethod sample(ID As %String) As %String
and adding after the %Save():
   s ID=$lg(Name.%Oid(),1)

Then the main program could look something like (the leading dot passes ID by reference so the value can be returned):
   s ID=""
   s status=##class(Eam.Empdetails).sample(.ID)
   if (status=1) {
      s Name=##class(Eam.Empdetails).%OpenId(ID)
      w !
      w "EmpName: "_Name.EmpName,!
      w "Address: "_Name.Address,!
      w "Phone: "_Name.PhoneNo,!
      w "Id: "_Name.id,!
   }

If you want to check the status with $$$OK (or other predefined values), add the following line to the top of the main program:
   #include %occStatus
and change the test to:
   if (status=$$$OK) {

There are other ways to see your data, such as with SQL (add a WHERE clause if desired):
   SELECT * FROM Eam.Empdetails

It originally sounded like "posts" was a live table, receiving INSERTs, DELETEs and perhaps UPDATEs, and so there were suggestions such as firing a trigger whenever any of those happened (MySQL does support triggers). But it now seems like "posts" is generally static, and periodically updated/replaced.

So, to have the Caché version duplicate the MySQL version, could you similarly empty the "downloadedposts" table, and then load it with the latest backup that was used to update "posts"? This might only make sense if the database were small enough to use a MySQL logical backup: http://dev.mysql.com/doc/refman/5.7/en/backup-types.html. Perhaps you could reconsider this approach. Caché offers options (such as no journaling) to speed such loads.