You can use Entity SQL with Like instead of a lambda in your Where method, or if you're using a data source control you can use Like in a QueryExtender control.
Now you have defined a function that will insert the like expression in the "DefiningExpression" part above into the SQL generated by EF. But you need the following step to be able to call the function.
2) Make a static class in an available namespace. Put a static method like this in it:
[System.Data.Objects.DataClasses.EdmFunction("...", "String_Like")]
public static Boolean String_Like(this String searchingIn, String lookingFor)
{
throw new Exception("Not implemented");
}
Replace the "..." in the annotation with your context namespace. Now, when you call the function in a linq expression or lambda, the annotation will cause it to use the definition in the .edmx file.
3) Use the function like this:
var test = ent.<table_name>.Where(p => p.column.String_Like("%some%text%"));
I have done this in a test project and it works really well I must say. As I said, the drawback is the manual editing of EDMX XML, but it preserves the function when doing automatic updates of the edmx file.
/daphine
Marked as answer by ignatandrei on Feb 19, 2011 04:55 PM
Now here's my tested sample, please check it :) If you have some questions, you can give your feedback to me, or please send your email to
maledong@qq.com for your sample codes:)
using System;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Text;
using System.Xml.Linq;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Reflection.Emit;
using System.Reflection;
using System.Security.Permissions;
using System.Data;
using System.Xml;
namespace CSharpConsoleTest
{
public class Program {
static void Main(string[] args)
{
using (Database1Entities de = new Database1Entities())
{
//Starting with CD, end with BLUE, ignore case sensitive... Regex reg = new Regex("^CD.*BLUE$",RegexOptions.IgnoreCase);
var result = de.MyTables.AsEnumerable().Where((p) => reg.IsMatch(p.Contents));
Suppose you have written a stored procdure, something looks like this:
ALTER PROCEDURE dbo.MyLikeSearch AS SELECT ID, Contents FROM MyTable WHERE (Contents LIKE 'CD%BLUE') RETURN
And then open the edmx file to import the stored procdure into the EntityFramework.
Then you can code like this:
using System;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Text;
using System.Xml.Linq;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Reflection.Emit;
using System.Reflection;
using System.Security.Permissions;
using System.Data;
using System.Xml;
namespace CSharpConsoleTest
{
public class Program : MarshalByRefObject
{
static void Main(string[] args)
{
using (Database1Entities de = new Database1Entities())
{ var result = de.FindAllCDBlue(); //Find AllCDBlue is the name I gave
foreach (var item in result)
{
Console.WriteLine(item.Contents);
}
}
}
}
}
Because this is a pre-compiled procdure, I hope this will raise the speed than I've done before. Please have a try and hope this helps. Thanks for your supporting.
ignatandrei
All-Star
134983 Points
21638 Posts
Moderator
MVP
Entity framework escaping like %
Feb 16, 2011 09:57 PM|LINK
Let's say I have a single table Product with ID and Name
I would like the user to search for a text - so I made:
.Where(item=>item.Contains(SearchText))
When the SearchText is "CD" - all is good.
However, I would like to let the user search with % - like searching for : CD%BLUE - like Name should contain CD and after some char the word BLUE
The problem is that EF is escaping the % - and , by that , it search for literal %, not for the meaning of % in SQL
Do you know any solution ?
tdykstra
Contributor
4450 Points
624 Posts
Microsoft
Moderator
Re: Entity framework escaping like %
Feb 16, 2011 10:05 PM|LINK
You can use Entity SQL with Like instead of a lambda in your Where method, or if you're using a data source control you can use Like in a QueryExtender control.
See "Using the "Like" Operator to Filter Data" in http://www.asp.net/entity-framework/tutorials/the-entity-framework-and-aspnet-–-getting-started-part-3
ignatandrei
All-Star
134983 Points
21638 Posts
Moderator
MVP
Re: Entity framework escaping like %
Feb 17, 2011 09:42 AM|LINK
It's an EF question, not an EntityDataSource question
So it's a wrong answer.
daphine
Member
370 Points
53 Posts
Re: Entity framework escaping like %
Feb 17, 2011 12:29 PM|LINK
Either split the string and use multiple contains like so:
foreach (string s in splitstrings)
{
query = query.Where(item=>item.Contains(s));
}
or you can add a like function definition to your .edmx file, something like this post describes:
http://jendaperl.blogspot.com/2011/02/like-in-linq-to-entities.html
Unfortunately, SQLFunctions.Like is unavailable for link to E.
ignatandrei
All-Star
134983 Points
21638 Posts
Moderator
MVP
Re: Entity framework escaping like %
Feb 17, 2011 02:24 PM|LINK
daphine, thank you.
1. The Where splitting I have thought myself, but it's not so good : it will find, for search text CD%Blue , also BLUE%CD
Could you improve?
2. the link shows a generating function that
throw new Exception("Not implemented");
I do not understand the post...Could you explain, please?
daphine
Member
370 Points
53 Posts
Re: Entity framework escaping like %
Feb 18, 2011 06:52 AM|LINK
2. works like a charm! If you do it correctly and use it in a linq context it will never throw that exception. I'll explain it a little bit further.
Drawback is that you have to manually edit the XML in your .EDMX file.
So, here's how you do it:
1) Put a definition of a linq function in your .EDMX file. It has to reside within the
<edmx:ConceptualModels>
<Schema Namespace="...">
tag.
The function definition should look something like this:
<Function Name="String_Like" ReturnType="Edm.Boolean"> <Parameter Name="searchingIn" Type="Edm.String" /> <Parameter Name="lookingFor" Type="Edm.String" /> <DefiningExpression> searchingIn LIKE lookingFor </DefiningExpression> </Function>Now you have defined a function that will insert the like expression in the "DefiningExpression" part above into the SQL generated by EF. But you need the following step to be able to call the function.
2) Make a static class in an available namespace. Put a static method like this in it:
[System.Data.Objects.DataClasses.EdmFunction("...", "String_Like")] public static Boolean String_Like(this String searchingIn, String lookingFor) { throw new Exception("Not implemented"); }Replace the "..." in the annotation with your context namespace. Now, when you call the function in a linq expression or lambda, the annotation will cause it to use the definition in the .edmx file.
3) Use the function like this:
var test = ent.<table_name>.Where(p => p.column.String_Like("%some%text%"));
I have done this in a test project and it works really well I must say. As I said, the drawback is the manual editing of EDMX XML, but it preserves the function when doing automatic updates of the edmx file.
/daphine
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Entity framework escaping like %
Feb 18, 2011 08:20 AM|LINK
Now here's my tested sample, please check it :) If you have some questions, you can give your feedback to me, or please send your email to maledong@qq.com for your sample codes:)
using System;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Text;
using System.Xml.Linq;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Reflection.Emit;
using System.Reflection;
using System.Security.Permissions;
using System.Data;
using System.Xml;
namespace CSharpConsoleTest
{
public class Program {
static void Main(string[] args)
{
using (Database1Entities de = new Database1Entities())
{
//Starting with CD, end with BLUE, ignore case sensitive...
Regex reg = new Regex("^CD.*BLUE$",RegexOptions.IgnoreCase);
var result = de.MyTables.AsEnumerable().Where((p) => reg.IsMatch(p.Contents));
foreach (var item in result)
{
Console.WriteLine(item.Contents);
}
}
}
}
}
ignatandrei
All-Star
134983 Points
21638 Posts
Moderator
MVP
Re: Entity framework escaping like %
Feb 18, 2011 09:36 AM|LINK
@ decker dong : it's an entity framework question .
please try your answer with a database
daphine
Member
370 Points
53 Posts
Re: Entity framework escaping like %
Feb 18, 2011 09:54 AM|LINK
Have you tried my suggestion? Works fine with SQL server edmx, I have tested it myself.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Entity framework escaping like %
Feb 19, 2011 08:14 AM|LINK
What about trying another way?
Suppose you have written a stored procdure, something looks like this:
ALTER PROCEDURE dbo.MyLikeSearch
AS
SELECT ID, Contents
FROM MyTable
WHERE (Contents LIKE 'CD%BLUE')
RETURN
And then open the edmx file to import the stored procdure into the EntityFramework.
Then you can code like this:
using System;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Text;
using System.Xml.Linq;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Reflection.Emit;
using System.Reflection;
using System.Security.Permissions;
using System.Data;
using System.Xml;
namespace CSharpConsoleTest
{
public class Program : MarshalByRefObject
{
static void Main(string[] args)
{
using (Database1Entities de = new Database1Entities())
{
var result = de.FindAllCDBlue(); //Find AllCDBlue is the name I gave
foreach (var item in result)
{
Console.WriteLine(item.Contents);
}
}
}
}
}
Because this is a pre-compiled procdure, I hope this will raise the speed than I've done before. Please have a try and hope this helps. Thanks for your supporting.
Besides I've sent my codes for you to see again:)
:)