Query: FromSql not composing with LINQ when it contains certain whitespace patterns

[ra0o0f]

Hi

Following FromSql query does not compose with LINQ. No exception is raised and the result is correct BUT Sql Server Profiler shows that Where clause is not composing with FromSql query.

var result = dbContext.Permission
                .FromSql($@"
select
p.*
from Permission p
inner join
(
    select 
    r.RoleId 
    from 
    Role r
    where 
    Parents.exist('//I[.=sql:variable(""{{0}}"")]') = 1
    union all
    select {{0}} RoleId
) roles
on p.RoleId = roles.RoleId
", roleId)
                    .Where(c =>
                        c.Entity == entity &&
                        c.RoleId == roleId &&
                        c.Type == type &&
                        c.EntityId == entityId)
                        .ToList()

but the query in Sql Server Profiler is:

exec sp_executesql N'
select
p.*
from Permission p
inner join
(
    select 
    r.RoleId 
    from 
    Role r
    where 
    Parents.exist(''//I[.=sql:variable("@p0")]'') = 1
    union all
    select @p0 RoleId
) roles
on p.RoleId = roles.RoleId
',N'@p0 int',@p0=170

which shows Where clause is not included in query

EF Core version:1.0.1
Operating system: windows 10
Visual Studio version: vs 2015

[rowanmiller]

Can you share a complete code listing (or project) that we can run to see the issue? We need to know what the various properties etc. in the model are to work out why this is being client evaluated.

[ra0o0f]

@rowanmiller
complete code listing:

Entities:

    public class Role
    {
        public int RoleId { get; set; }

        public string Title { get; set; }

        public string Parents { get; set; }
    }

    public enum PermissionEntity
    {
        AttachmentCollection = 1,
        Category = 3,
    }

    public class Permission
    {
        public int PermissionId { get; set; }

        public PermissionEntity Entity { get; set; }

        public int RoleId { get; set; }

        public int EntityId { get; set; }
    }

DbContext:

    public class EfContext : DbContext
    {
        string connectionString;

        public EfContext(string connectionString) : base()
        {
            this.connectionString = connectionString;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connectionString);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Role>();
            modelBuilder.Entity<Permission>();
        }
    }

connection string

<add name="TestContext" connectionString="Data Source=.;Initial Catalog=EFTestDB;Integrated Security = true;" providerName="System.Data.SqlClient" />

LINQ query:

            string cnn = ConfigurationManager.ConnectionStrings["TestContext"].ConnectionString;

            int roleId = 77;


            using (EfContext context = new EfContext(cnn))
            {
                var result = context.Set<Permission>()
                .FromSql($@"
select
p.*
from Permission p
inner join
(
    select 
    r.RoleId 
    from 
    Role r
    where 
    Parents.exist('//I[.=sql:variable(""{{0}}"")]') = 1
    union all
    select {{0}} RoleId
) roles
on p.RoleId = roles.RoleId
", roleId)
                    .Where(c =>
                        c.Entity == PermissionEntity.AttachmentCollection &&
                        c.RoleId == roleId &&
                        c.EntityId == 22)
                        .ToList();
            }

Tables:

CREATE TABLE [dbo].[Permission](
    [PermissionId] [int] IDENTITY(1,1) NOT NULL,
    [Entity] [int] NOT NULL,
    [RoleId] [int] NOT NULL,
    [EntityId] [int] NOT NULL,
 CONSTRAINT [PK_Permission] PRIMARY KEY CLUSTERED 
(
    [PermissionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Role](
    [RoleId] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](250) NOT NULL,
    [Parents] [xml] NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
    [RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[smitpatel]

Query composition did not work because the Sql started with new line character and there is no space after Select. 😞