New Posts New Posts RSS Feed: EntitySQL and include
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

EntitySQL and include
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
danielp37 View Drop Down
Newbie
Newbie


Joined: 18-Mar-2008
Location: United States
Posts: 29 		Post Options Post Options   Quote danielp37 Quote  Post ReplyReply Direct Link To This Post Topic: EntitySQL and include
    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
Back to Top
robertg View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 15-Mar-2011
Location: California
Posts: 87 		Post Options Post Options   Quote robertg Quote  Post ReplyReply Direct Link To This Post 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();
Back to Top
danielp37 View Drop Down
Newbie
Newbie


Joined: 18-Mar-2008
Location: United States
Posts: 29 		Post Options Post Options   Quote danielp37 Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
robertg View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 15-Mar-2011
Location: California
Posts: 87 		Post Options Post Options   Quote robertg Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
danielp37 View Drop Down
Newbie
Newbie


Joined: 18-Mar-2008
Location: United States
Posts: 29 		Post Options Post Options   Quote danielp37 Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
robertg View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 15-Mar-2011
Location: California
Posts: 87 		Post Options Post Options   Quote robertg Quote  Post ReplyReply Direct Link To This Post 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
 
 
Back to Top
danielp37 View Drop Down
Newbie
Newbie


Joined: 18-Mar-2008
Location: United States
Posts: 29 		Post Options Post Options   Quote danielp37 Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down