Whilst not directly helping OP I keep coming back to this question over the years and have developed another trick you can use if your current schema is close to the legacy database.
This will only apply if you can create a query that is similar or the same against the EF context, we will be exploiting the Linq to Entity SQL table alias conventions and as such it may be affected by future updates.
- define an EF query that closely approximates the table structure of your output.
- use
FilterQueryOption.ApplyTo() to apply just the $filter to the approximated query
- Capture the SQL string from the query
- Extract the
WHERE clause from the query
- Inject the
WHERE clause into your custom query.
Apart from being tied to the table alias constraints the EF injects, this offers a lot of security and flexibility over using REGEX alone. you might find that you can use regex to further enhance this output, however the OData parser will already validate and sanitise the URL expression into valid SQL syntax, including transposing the expression into SQL function calls.
The following is based on EF6 and OData v4, so the URL syntax is a bit different, but the same concept should apply to previous versions of ODataLib as well.
CustomDTO is a custom class, not defined in the EF DbContext model.Customer IS defined in the EF DbContext, it has similar fields to the legacy database
/// <summary>Return a list of customer summaries for a given Account</summary>
[EnableQuery, HttpGet]
public IQueryable<CustomDTO> Customers([FromODataUri] int key, ODataQueryOptions<CustomDTO> _queryOptions)
{
// The custom query we want to apply to the legacy database.
// NOTE: If the fields are identical to the current DbContext, then we don't have to go this far.
// We MUST alias the table to match the generated SQL
string sql = "SELECT CustName, IsNull(Address1,'') + IsNull(Address2,'') as Address, Phone " +
"FROM Customers AS [Extent1]" +
"WHERE AccountId = @AccountId";
if (!String.IsNullOrWhiteSpace(_queryOptions.Filter?.RawValue))
{
var criteriaQuery = from x in db.Customers
select new CustomDTO
{
Name = CustName,
Address = Address1 + Address2
Phone = Phone
};
var modifiedQuery = _queryOptions.Filter.ApplyTo(criteriaQuery, new ODataQuerySettings({ EnableConstantParameterization = false });
string modifiedSql = modifiedQuery.ToString();
modifiedSql = modifiedSql.Substring(modifiedSql.LastIndexOf("WHERE ") + 5);
sql += $" AND ({modifiedSql})";
}
var customers = aDifferentContext.Database.SqlQuery<CustomDTO>(sql, new SqlParameter("@AccountId", key)).ToList();
return customers.AsQueryable();
}
- An alternative to using the alias
[Extent1] in our custom query would be to use string replacement, but this works well enough.
EnableConstantParameterization was deliberately disabled, to inline the filter values, instead of having to track and inject the SqlParameter for each filter argument. It similifies the code and is already sanitised to a degree. Its up to you to put in the extra effort if this does not satisfy your security concerns.- You will notice I filtered to the LAST
WHERE clause in the query, that is because if this query involved projections and the caller tried to apply a filter to one of the secondary extents (joined result sets) then EF will optimise the query by filtering the sub query, rather than applying the filters all at the end. There are ways around this or to work with it, for now lets stick to a simple example.
SQL Generated by modifiedQuery:
URL: ~/OData/Accounts(1102)/Customers?$filter=startswith(Name, 'Bill') and contains(Address, 'sunset') and contains(Phone, '7421')
Filter.RawValue: startswith(Name, 'Bill') and contains(Address, 'sunset') and contains(Phone, '7421')
SELECT
[Extent1].[CustName] AS [Name],
CASE WHEN ([Extent1].[Address1] IS NULL) THEN N'' ELSE [Extent1].[Address1] END + CASE WHEN ([Extent1].[Address2] IS NULL) THEN N'' ELSE [Extent1].[Address2] END AS [C1],
[Extent1].[Phone] AS [Phone]
FROM [dbo].[Customer] AS [Extent1]
WHERE ([Extent1].[CustName] LIKE 'Bill%')
AND (CASE WHEN ([Extent1].[Address1] IS NULL) THEN N'' ELSE [Extent1].[Address1] END
+ CASE WHEN ([Extent1].[Address2] IS NULL) THEN N'' ELSE [Extent1].[Address2] END
LIKE N'%sunset%')
AND ([Extent1].[Phone] LIKE '%7421%')
The final SQL that gets executed:
SELECT CustName as Name, IsNull(Address1,'') + IsNull(Address2,'') as Address, Phone
FROM [dbo].[Customer] AS [Extent1]
WHERE AccountId = @AccountId AND (([Extent1].[CustName] LIKE 'Bill%')
AND (CASE WHEN ([Extent1].[Address1] IS NULL) THEN N'' ELSE [Extent1].[Address1] END
+ CASE WHEN ([Extent1].[Address2] IS NULL) THEN N'' ELSE [Extent1].[Address2] END
LIKE N'%sunset%')
AND ([Extent1].[Phone] LIKE '%7421%'))
Class Definitions
public class CustomDTO
{
public string Name { get;set; }
public string Address { get;set; }
public string Phone { get;set; }
}
public class Customer
{
public int AccountId { get;set; }
public string CustName { get;set; }
public string Address1 { get;set; }
public string Address2 { get;set; }
public string Phone { get;set; }
}
I use this trick mostly when optimising complex Linq expressions that return DTO structures that can be implemented with much simpler SQL than EF ca produce. What was a traditional EF query is replaced with a raw SQL query in the form of DbContext.Database.SqlQuery<T>(sql, parameters)
In this example I used a different EF DbContext, but once you have the SQL script, you should be able to run that however you need to.
