Execute Raw SQL with EF Core #3559

bilalhaidar created 6 years ago

Hi, I have a bit complex query that I know how to write in SQL but not in LINQ-To-SQL.

Can I use the DbSset.FromSql() method to execute Raw SQL in a custom repository? (ASP.NET Core / Angular 2).

I dunno if you can help with the linq-to-sql.

I have the following setup:

OU (Region) with Parent Null OU (Camp) with Parent OU Region

This could be repeated many times in a sense I will have many regions and many camps within. (2 level only)

I have an entity that needs to store the OU Id.

Upon creation of this new entity, if the entity belongs to a region only, then OU (Region) Id is stored on the entity. If however, this entity belongs to an OU (Region) and OU (Camp), then the OU (Camp) Id is stored on the entity.

A user would have access (belongs) to OUs. if he belongs to an OU (Region) he/she can see all entities created within the O?OU (Region) and OU(Camp)s within the region. If he/she has access to a OU (Camp) only, he/she can see entities created in that OU (Camp) only.

Here's the SQL Query (entity in this case Blog Post). Can it be done in LINQ? Otherwise, can I use FromSql() method?

Thanks

DECLARE @code NVARCHAR(100) = '00001%'
DECLARE @user INT = 1;

WITH UserOus(OuId) AS
(
	SELECT 
		[ou].[Id]
	FROM
		[AbpUserOrganizationUnits] AS [ous]
	LEFT OUTER JOIN [AbpOrganizationUnits] AS [ou] ON [ou].Id = [ous].OrganizationUnitId
	WHERE [ous].UserId = @user
)
SELECT [bp].[Id], [bp].[Title], [bp].[OrganizationUnitId]
FROM [BlgPosts] AS [bp]
INNER JOIN [AbpOrganizationUnits] AS [ou] ON [bp].[OrganizationUnitId] =[ou].[Id]
INNER JOIN [UserOus] AS [uous] ON [ou].ParentId = [uous].[OuId] OR [ou].[Id] = [uous].OuId
GO 


SELECT TOP (1000) [Id]
      ,[TenantId]
      ,[ParentId]
      ,[Code]
      ,[DisplayName]
      ,[IsDeleted]
      ,[DeleterUserId]
      ,[DeletionTime]
      ,[LastModificationTime]
      ,[LastModifierUserId]
      ,[CreationTime]
      ,[CreatorUserId]
  FROM [MainApp].[dbo].[AbpOrganizationUnits]

SELECT TOP (1000) [Id]
      ,[CategoryId]
      ,[Title]
      ,[Content]
      ,[Tags]
      ,[Status]
      ,[CreationTime]
      ,[CreatorUserId]
      ,[OrganizationUnitId]
  FROM [MainApp].[dbo].[BlgPosts]
  
  SELECT TOP (1000) [Id]
      ,[UserId]
      ,[OrganizationUnitId]
      ,[CreationTime]
      ,[CreatorUserId]
      ,[TenantId]
  FROM [MainApp].[dbo].[AbpUserOrganizationUnits]

Execute Raw SQL with EF Core #3559

5 Answer(s)