Entity Sql query many to many relationship + tpt inheritance problem

Question

I have a model with tpt inheritance between Person (base) and Guest (derived) entities. There is also many to many relationship between entities Person and Company.

I have a working LINQ to Entities query, but I need to get this same query working in ESQL with ObjectServices and with EntityClient.
I need to get name of the guest and the name of his company, for every one that has guested more than 1 time.

I would appreciate complete query with printing out these two data.

Here is the LINQ query :

var persons = from c in context.Person.OfType<Guest>()
                            from company in c.Company
                            where c.timesGuested>1
                            select new {c, company};

foreach(var person in persons)
{
                            Console.WriteLine("{0} {1}, person.c.Name.Trim(), person.company.Name);
}

Answer 1

Hi Vladimir,

 

The equivalent query in EntitySQL would look something like:

SELECT c, company

FROM OfType(Container.PersonSet, Guest) as c CROSS APPLY c.Company as c

WHERE c.timesGuested > 1

 

Please note that you would have to adjust ‘Container.PersonSet’ to match the container/entity set and these may differ when the query is issued against Entity Client and the object context.  You also may need to prefix the type Guest with the namespace.  The EntityClient version would have to match the names specified in your CSDL file (or the CSDL portion of the EDMX) and the object context one names specified in the object context class (e.g. the generated code class).

 

I hope that helps.

Thanks,

Kati

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

EntityClient :

SELECT c, company

FROM OfType(Container.PersonSet, Guest) as c CROSS APPLY Container .Company as c

WHERE c.timesGuested > 1

I get foolowing error: The alias 'c' was already used. in the current FROM clause, near simple identifier

Then I've tried with (I guessed you might have made a small mistake in your query) :

SELECT c, company

FROM OfType(Container.PersonSet, Guest) as c CROSS APPLY Container .Company as company

WHERE c.timesGuested > 1

and then for the following code to print value

EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = queryString;
using (EntityDataReader rdr =
     cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
     {
           while (rdr.Read())
           {
                Console.WriteLine("{0}", rdr.GetString(0));

get the following error when I try to print:

Unable to cast object of type 'System.Data.Query.ResultAssembly.BridgeDataRecord' to type 'System.String'



ObjectContext :

how am I suppose to pring out the results ?

var persons = context.CreateQuery<DbDataRecord>(qStr);

foreach (var person in persons)
{
??? now what ???
how do I print out Person.Name and Company.Name  ???

Do I need to cast DbDataRecord result ? And how do I do that exactly cause I could not find it... and I have two Entities returned c representing Person and company representing Company

Answer 3

With entity client when you retrieve an entity, that entity will be represented by a DataRecord embedded in a column within the outer DataRecord.  So, the exception you are getting is saying that you are retrieving the first column as a string when it's actually an object which is a datarecord.  So probably you could change your sample code to be something like this:

EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = queryString;
using (EntityDataReader rdr =
     cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
     {
           while (rdr.Read())
           {
                var guestRecord = (DbDataRecord) rdr.GetObject(0);
                Console.WriteLine("{0}", guestRecord.GetString(0));
                var companyRecord = (DbDataRecord) rdr.GetObject(1);
                ...

When you do this with ObjectServices, the difference is that the entities are materialized into objects instead of data records, so the code would be something like this:

var persons = context.CreateQuery<DbDataRecord>(qStr);

foreach (var record in persons)
{
    var guest = (Guest) record.GetObject(0);
    var company = (Company) record.GetObject(1);

    Console.WriteLine(guest.Name);
    Console.WriteLine(company.Name);
}

- Danny

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 4

for some reason i do not have getObject method so I get following errors for ObjectContext and EntityClient

Error    1    'System.Data.Common.DbDataRecord' does not contain a definition for 'GetObject' and no extension method 'GetObject' accepting a first argument of type 'System.Data.Common.DbDataRecord' could be found (are you missing a using directive or an assembly reference?)    C:\Users\Vladimir\Documents\Visual Studio 2008\Projects\PrimerZaEntity\PrimerZaEntity\Program.cs    82    46    PrimerZaEntity


Error    2    'System.Data.EntityClient.EntityDataReader' does not contain a definition for 'GetObject' and no extension method 'GetObject' accepting a first argument of type 'System.Data.EntityClient.EntityDataReader' could be found (are you missing a using directive or an assembly reference?)    C:\Users\Vladimir\Documents\Visual Studio 2008\Projects\PrimerZaEntity\PrimerZaEntity\Program.cs    115    61    PrimerZaEntity


here are the using directives :

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;

using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.EntityClient;
using System.Data.Common;

Answer 5

Sorry I was in a hurry and just typed from memory rather than looking up the command or writing a simple repro.  What you really want is GetValue rather than GetObject.

- Danny

---

This posting is provided "AS IS" with no warranties, and confers no rights.