Entity Framework : How to use SQL Server View by EF

Question

suppose below code i will write in sql server view and access just like table in EF. if anyone notice the below code then must notice i hard code the user id value in join which is not my criteria.

select c.ColorID
    , c.ColorName
    , IsSelected = case when uc.ColorID is null then 0 else 1 end
from dbo.Colors c
left join dbo.UserColor uc on uc.ColorID = c.ColorID and uc.UserID = 1 --leave this in the join or it becomes an inner join
left join dbo.Users u on u.UserID = uc.UserID

so how could i write a view and call from EF where i will set user id in my code which will be used same like above code means user id value will be set in join not in where clause because if i set value in where clause then i will not get desired output.

when i set value for userid in where clause then i get single row instead of multiple which is not my requirement. i want all color details will be shown as well as user selected one.

i know people would suggest me to create store procedure but i have some constraint so i have to achieve it by view in sql server. so please suggest me how to handle this situation with view. thanks

Answer 1

Hi Mou_inn,

This forum is to discuss entity framework related issue, as your issue is more related to T-SQL, we'll move your thread to T-SQL forum for support.

Thank you for your understanding.

Best regards,

Cole Wu

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi Mou_inn,

I have made a demo as following code shows and you could have a look at it.  In your case, you could let the View return the sub-query of the last query in following code.

declare @Colors table (ColorID int, ColorName varchar(20))
insert into @Colors values (1,'Black'),(2,'Red'),(3,'White')

declare @Users table (UserID int, UserName varchar(20))
insert into @Users values (1,'User A'),(2,'User B'),(3,'User C')

declare @UserColor table (UserID int,ColorID int)
insert into @UserColor values (1,1),(1,2),(1,3),(2,1),(2,3),(3,2),(3,3)

--This is your code
select c.ColorID
    , c.ColorName
    , IsSelected = case when uc.ColorID is null then 0 else 1 end
from @Colors c
left join @UserColor uc on uc.ColorID = c.ColorID and uc.UserID = 3 --leave this in the join or it becomes an inner join
left join @Users u on u.UserID = uc.UserID

--This is my code
;with cte(ColorID,ColorName,UserId,UserName) 
as
(
select * from @Colors cross join @Users
)

select 
	ColorID, 
	ColorName,
	IsSelected = case when FUserID is null then 0 else 1 end
from --You could let the View return this sub query 
(
	select 
		cte.ColorID,cte.ColorName,cte.UserID, uc.UserID as FUserID
	from 
		cte left join @UserColor uc on uc.ColorID = cte.ColorID and uc.UserID = cte.UserID
) a
where UserID = 3

Best Regards,

Albert Zhang

Answer 3

Well, to keep it a view you can change it to

select c.ColorID
    , c.ColorName
    , IsSelected = case when uc.ColorID is null then 0 else 1 end
from dbo.Colors c
left join dbo.UserColor uc on uc.ColorID = c.ColorID
left join dbo.Users u on u.UserID = uc.UserID
WHERE uc.UserId = @UserId or uc.UserId  IS NULL

So, the top portion will be a view (with the added UserId column) and the WHERE condition you would add to select from that view.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles