2

I have the following simplified query(currently being returned from a stored procedure) that I want to use as the basis for a view for work in Entity Framework:

Select 
cast (case when warehouse = @Warehouse then 1 else 0 end as bit) as AtThisWarehouse,    
s.id as productStatusId 
from products p 
left join productstatus s on p.availiblity = s.cd;

Obviously I can't use a parameter in a view and don't want to use a stored procedure or user defined function since I want to follow current pattern in Entity Framework.

The Warehouse will be known and can be passed to be used in the where clause via filters but I am not sure how to leverage that fact to get back the results I want. Any suggestions on how to go about this?

Bret Van Hof
  • 31
  • 3
  • `CASE` **expression**; you won't wide any `Case` (`Switch`) statements in T-SQL as they don't exist in the language. You cannot, however, parametrise a `VIEW`; a`VIEW` is simply a virtual table defined by a query. If you need to use a variable/parameter when using a `VIEW` you do it in the outer query: `SELECT * FROM YourView WHERE ViewColumn = @Parameter` – Thom A Dec 24 '19 at 17:36
  • I don't understand. This seems like a clear use-case for a UDF. Otherwise, if you can pass a value in the `where` clause, why not in the `select`? – Gordon Linoff Dec 24 '19 at 17:59

1 Answers1

1

So instead of using the case statement with the parameter, use it as your filter. So your view would be something like (best practice is to always include schema on your objects, I assumed dbo):

view

select warehouse
     , productStatusId
     , productId
from dbo.products p
left join dbo.productstatus s on p.availability = s.cd;

query to use view

select productId
 , productStatusId
from schema.viewname
where warehouse = @warehouse;

another way to get all warehouse statuses

select cast(case when warehouse = @warehouse then 1 else 0 end as bit) as AtThisWarehouse, 
productStatusId
from schema.viewname;
LaraRaraBoBara
  • 628
  • 1
  • 6
  • 15
  • I need to know status at all warehouses in addition to knowing if the status applies to given warehouse... – Bret Van Hof Dec 24 '19 at 18:07
  • Your view would give that to you if you use select * from schema.viewname. You can also use a case statement (similar to your view above) when using a select statement against a view. A view acts just like a table. – LaraRaraBoBara Dec 24 '19 at 18:09
  • Is there a way to have Entity Framework create the select statement with the case in it though? Otherwise I am back to ADO and may as well use a stored proc... – Bret Van Hof Dec 24 '19 at 18:22
  • 1
    Looks Like I can do something like var query = from t in context.MyTable group t by t.Code into grp select new { Code = grp.Key, Jan = grp.Sum(x => x.Month == 1 ? x.Days : 0), }; – Bret Van Hof Dec 24 '19 at 18:34
  • 1
    see https://stackoverflow.com/questions/20496919/what-is-the-equivalent-of-case-when-then-t-sql-with-entity-framework – Bret Van Hof Dec 24 '19 at 18:42