How do I have to build my query to result in an output SQL query like:
SELECT
[viewRegisters].[Id] AS [IdRegister]
WHERE Name LIKE '%a%bc'
OR
SELECT
[viewRegisters].[Id] AS [IdRegister]
WHERE Name LIKE 'a%b%c'
OR
SELECT
[viewRegisters].[Id] AS [IdRegister]
WHERE Name LIKE 'a%b%c%'
I'm using .Net Framework 4.0, Entity Framework v4.1 and C#.
EF v4.1 converts this type of linq queries from:
((IQueryable<T>)Data).Where(z => z.Field.Contains("a%b%c%"));
Into:
SELECT
[viewRegisters].[Id] AS [Id]
WHERE Name LIKE N'a~%b~%c~%' ESCAPE N'~'
That's not what I want. I want to be able to use the 'percent' symbol as I do directly in DB.
You must use ESQL if you want full wildcard support. Linq-to-entities is not able to do that and EFv4.1 code first (without EDMX) doesn't have support for model defined functions so the solution provided by @Johann Blais cannot be used.
I guess the code to run ESQL query can look like:
string command = "SELECT VALUE e FROM ContextName.DbSetName AS e WHERE e.Field LIKE 'a%b%c%'"
ObjectContext ctx = ((IObjectContextAdapter)dbContext).ObjectContext;
ObjectQuery<EntityType> query = new ObjectQuery<EntityType>(command, ctx);
ObjectResult<EtntiyType> result = query.Execute(MergeOption.AppendOnly);
If you are using SQL Server, use the PATINDEX
function to do a pattern search. You can access this function through EF using the SqlFunctions
class.
For example, the following EF query
context.ViewRegisters.Where(z => SqlFunctions.PatIndex("a%b%c%", z.Name) > 0);
will translate into
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name]
FROM [dbo].[ViewRegisters] AS [Extent1]
WHERE (CAST(PATINDEX(N'a%b%c%', [Extent1].[Name]) AS int)) > 0
var query = from viewRegister in context.ViewRegisters
where viewRegister.Name.Contains("yourname")
select viewRegister;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With