Print Page | Close Window

EntitySQL and include

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2819
Printed Date: 15-Oct-2026 at 1:32pm


Topic: EntitySQL and include
Posted By: danielp37
Subject: EntitySQL and include
Date 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


Replies:
Posted By: robertg
Date 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();

Posted By: danielp37
Date 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.

Posted By: robertg
Date 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();

 
 

Posted By: danielp37
Date 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

Posted By: robertg
Date 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
 
 

Posted By: danielp37
Date 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


Print Page | Close Window