DevForce Community Forum : EntitySQL and include

EntitySQL and include : That's what I had observed...

Mon, 25 Jul 2011 15:06:38 -700

Author: danielp37
Subject: 2819
Posted: 25-Jul-2011 at 3:06pm

That's what I had observed from my own testing but I was hoping that there was something I had overlooked.

Thanks,

Dan

EntitySQL and include : Dan, Sorry, I misread your...

Mon, 25 Jul 2011 15:00:42 -700

Author: robertg
Subject: 2819
Posted: 25-Jul-2011 at 3:00pm

Dan,

Sorry, I misread your initial post. Otherwise, I wouldn't have flooded your inbox with examples that you clearly don't need; I apologize.

No, I'm afraid there is not, since the PassthruEsqlQuery class requires the returntype in every overload, and would not know how to handle data returned in multiple types.

Yours,

Robert

EntitySQL and include : I've already got the query...

Mon, 25 Jul 2011 14:49:30 -700

Author: danielp37
Subject: 2819
Posted: 25-Jul-2011 at 2:49pm

I've already got the query working in Entity SQL with parameters. My question is if there is a way to include other tables in the ParamaterizedEsqlQuery to eager load them rather than having to query for them individually after you get the entities back?

Dan

EntitySQL and include : If I understand correctly, you're...

Mon, 25 Jul 2011 14:25:37 -700

Author: robertg
Subject: 2819
Posted: 25-Jul-2011 at 2:25pm

If I understand correctly, you're allowing the end user to specify search criterion with wildcards, and then sending that directly to SQL in a command string or parameters? I don't believe that there's a way to accomplish this with LINQ in DevForce or EF. However, it may work if you use an Entity SQL query instead. For example, the following query against the NorthwindIB database should return one result:

NorthwindIBEntities manager = new NorthwindIBEntities();

var query = new PassthruEsqlQuery(typeof(Employee),

"SELECT VALUE E from Employees as E where E.FirstName like '%nd%w'");

var result = query.With(manager).Execute().ToList();

Data validation and security is, of course, probably even more important with ESQL than with LINQ. As that's the case, you'll want to be sure to use this with a ParameterizedQuery. Here's how you do this:

string myQueryArgument = "%ndr%w";

// TODO: validation logic for customer input

var myParameterizedquery = new ParameterizedEsql("SELECT VALUE E from Employees as E where E.FirstName like @name", new QueryParameter("name", myQueryArgument));

var myQuery = new PassthruEsqlQuery(typeof(Employee), myParameterizedquery);

var myResult = myQuery.With(manager).Execute().ToList();

Edited by robertg - 25-Jul-2011 at 2:28pm

EntitySQL and include : That won't work because the...

Mon, 25 Jul 2011 12:23:33 -700

Author: danielp37
Subject: 2819
Posted: 25-Jul-2011 at 12:23pm

That won't work because the user is able to choose where the '%' is placed and could actually place multiple '%'s in the search string thus I have to use ESQL to get the ability to fully use the like operator and with ESQL queries it doesn't allow you to use .includes so I was wondering if there is another way to include additional tables via ESQL.

EntitySQL and include : If you're using .include...

Mon, 25 Jul 2011 11:55:21 -700

Author: robertg
Subject: 2819
Posted: 25-Jul-2011 at 11:55am

If you're using .include in your query, it should be eager-loading all of the related entities in the query that you're running. For your 'wildcard' operation, you can achieve the same thing by using a combination of StartsWith and EndsWith. For example, if you had the query 'select * from Address where Street like 'firstpart%lastpart', you could use:

List<Address> tests = manager.Addresses.ToList();

var test = manager.Addresses

.Where(a => a.Street.ToLower().StartsWith("firstpart"))

.Where(a => a.Street.ToLower().EndsWith("lastpart"))

.ToList();

EntitySQL and include : Is there a way toeager load additional...

Tue, 12 Jul 2011 12:40:09 -700

Author: danielp37
Subject: 2819
Posted: 12-Jul-2011 at 12:40pm

Is there a way to eager load additional tables when querying via an Entity SQL query? The query isn't very complex but we are doing a search using the "like" operator and I haven't been able to find a way to do that in linq. I know that .StartsWith does a like 'Sometext%' but in this case the search needs to be able to put the '%' anywhere in the search string. There is one additional table that I want to bring in with the query results but haven't been able to figure out how to get Devforce to work with it. I found a way to do it in Entity SQL, but Devforce didn't like it as it appears that it only wants one entity returned per row but maybe I'm overlooking something.

Thank,

Dan Preece