c# - Full text index search for complex query in Entity framework -
i trying perform full text index search using ef 6.0. using idbcommandinterceptor (http://www.entityframework.info/home/fulltextsearch) perform full text search throwing me exception:
cannot use contains or freetext predicate on column 'firstname' because not full-text indexed.
linq query:
listofemployees = _context.employeecvs.include(x => x.employee) .include(x => x.tags) .include(x => x.projectexperiences) .where(x => x.employee.firstname.contains(searchquery.keyword) || x.employee.lastname.contains(searchquery.keyword) || x.projectexperiences.any(y => y.projecttitle.contains(searchquery.keyword) || y.description.contains(searchquery.keyword)) || x.tags.any(t => t.title.contains(searchquery.keyword))) .tolist(); below query executed ef:
exec sp_executesql n'select [unionall1].[id] [c1], [unionall1].[id1] [c2], [unionall1].[id2] [c3], [unionall1].[id3] [c4], [unionall1].[id4] [c5], [unionall1].[id5] [c6], [unionall1].[id6] [c7], [unionall1].[id7] [c8], [unionall1].[id8] [c9], [unionall1].[firstname] [c10], [unionall1].[lastname] [c11], [unionall1].[enterpriseid] [c12], [unionall1].[level] [c13], [unionall1].[c1] [c14], [unionall1].[id9] [c15], [unionall1].[id10] [c16], [unionall1].[title] [c17], [unionall1].[createddate] [c18], [unionall1].[createdby] [c19], [unionall1].[updateddate] [c20], [unionall1].[updatedby] [c21], [unionall1].[isdeleted] [c22], [unionall1].[tagtype_id] [c23], [unionall1].[projectexperience_id] [c24], [unionall1].[c2] [c25], [unionall1].[c3] [c26], [unionall1].[c4] [c27], [unionall1].[c5] [c28], [unionall1].[c6] [c29], [unionall1].[c7] [c30], [unionall1].[c8] [c31], [unionall1].[c9] [c32], [unionall1].[c10] [c33], [unionall1].[c11] [c34], [unionall1].[c12] [c35], [unionall1].[c13] [c36], [unionall1].[c14] [c37], [unionall1].[c15] [c38], [unionall1].[c16] [c39], [unionall1].[c17] [c40] (select case when ([join7].[fkemployeecvid] null) cast(null int) else 1 end [c1], [extent1].[id] [id], [extent2].[id] [id1], [extent3].[id] [id2], [extent4].[id] [id3], [extent5].[id] [id4], [extent6].[id] [id5], [extent7].[id] [id6], [extent1].[id] [id7], [extent1].[id] [id8], [extent4].[firstname] [firstname], [extent5].[lastname] [lastname], [extent6].[enterpriseid] [enterpriseid], [extent7].[level] [level], [join7].[id] [id9], [join7].[id] [id10], [join7].[title] [title], [join7].[createddate] [createddate], [join7].[createdby] [createdby], [join7].[updateddate] [updateddate], [join7].[updatedby] [updatedby], [join7].[isdeleted] [isdeleted], [join7].[tagtype_id] [tagtype_id], [join7].[projectexperience_id] [projectexperience_id], cast(null int) [c2], cast(null varchar(1)) [c3], cast(null int) [c4], cast(null varchar(1)) [c5], cast(null varchar(1)) [c6], cast(null datetime2) [c7], cast(null varchar(1)) [c8], cast(null datetime2) [c9], cast(null varchar(1)) [c10], cast(null bit) [c11], cast(null int) [c12], cast(null int) [c13], cast(null int) [c14], cast(null int) [c15], cast(null int) [c16], cast(null int) [c17] (select [var_41].[id] [id], [var_41].[employee_id] [employee_id] [dbo].[employeecv] [var_41] ([var_41].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent1] left outer join (select [var_42].[id] [id], [var_42].[firstname] [firstname], [var_42].[discriminator] [discriminator] [dbo].[employee] [var_42] ([var_42].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent2] on ([extent2].[discriminator] = n''employee'') , ([extent1].[employee_id] = [extent2].[id]) left outer join (select [var_43].[id] [id], [var_43].[lastname] [lastname], [var_43].[discriminator] [discriminator] [dbo].[employee] [var_43] ([var_43].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent3] on ([extent3].[discriminator] = n''employee'') , ([extent1].[employee_id] = [extent3].[id]) left outer join (select [var_44].[id] [id], [var_44].[firstname] [firstname], [var_44].[discriminator] [discriminator] [dbo].[employee] [var_44] ([var_44].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent4] on ([extent4].[discriminator] = n''employee'') , ([extent1].[employee_id] = [extent4].[id]) left outer join (select [var_45].[id] [id], [var_45].[lastname] [lastname], [var_45].[discriminator] [discriminator] [dbo].[employee] [var_45] ([var_45].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent5] on ([extent5].[discriminator] = n''employee'') , ([extent1].[employee_id] = [extent5].[id]) left outer join (select [var_46].[id] [id], [var_46].[enterpriseid] [enterpriseid], [var_46].[discriminator] [discriminator] [dbo].[employee] [var_46] ([var_46].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent6] on ([extent6].[discriminator] = n''employee'') , ([extent1].[employee_id] = [extent6].[id]) left outer join (select [var_47].[id] [id], [var_47].[level] [level], [var_47].[discriminator] [discriminator] [dbo].[employee] [var_47] ([var_47].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent7] on ([extent7].[discriminator] = n''employee'') , ([extent1].[employee_id] = [extent7].[id]) left outer join (select [extent8].[fkemployeecvid] [fkemployeecvid], [extent9].[id] [id], [extent9].[title] [title], [extent9].[createddate] [createddate], [extent9].[createdby] [createdby], [extent9].[updateddate] [updateddate], [extent9].[updatedby] [updatedby], [extent9].[isdeleted] [isdeleted], [extent9].[tagtype_id] [tagtype_id], [extent9].[projectexperience_id] [projectexperience_id] [dbo].[employeetags] [extent8] inner join (select [var_48].[id] [id], [var_48].[title] [title], [var_48].[createddate] [createddate], [var_48].[createdby] [createdby], [var_48].[updateddate] [updateddate], [var_48].[updatedby] [updatedby], [var_48].[isdeleted] [isdeleted], [var_48].[tagtype_id] [tagtype_id], [var_48].[projectexperience_id] [projectexperience_id] [dbo].[tag] [var_48] ([var_48].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent9] on [extent9].[id] = [extent8].[fktagid] ) [join7] on [extent1].[id] = [join7].[fkemployeecvid] (contains([extent2].[firstname], @p__linq__0)) or (contains([extent3].[lastname], @p__linq__1)) or ( exists (select 1 [c1] [dbo].[projectexperience] [extent10] (([extent10].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null)) , ([extent10].[type] = n''draft'') , ([extent1].[id] = [extent10].[employeecv_id]) , ((contains([extent10].[projecttitle], @p__linq__2)) or (contains([extent10].[description], @p__linq__3))) )) or ( exists (select 1 [c1] [dbo].[employeetags] [extent11] inner join (select [var_49].[id] [id], [var_49].[title] [title] [dbo].[tag] [var_49] ([var_49].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent12] on [extent12].[id] = [extent11].[fktagid] ([extent1].[id] = [extent11].[fkemployeecvid]) , (contains([extent12].[title], @p__linq__4)) )) union select 2 [c1], [extent13].[id] [id], [extent14].[id] [id1], [extent15].[id] [id2], [extent16].[id] [id3], [extent17].[id] [id4], [extent18].[id] [id5], [extent19].[id] [id6], [extent13].[id] [id7], [extent13].[id] [id8], [extent16].[firstname] [firstname], [extent17].[lastname] [lastname], [extent18].[enterpriseid] [enterpriseid], [extent19].[level] [level], cast(null int) [c2], cast(null int) [c3], cast(null varchar(1)) [c4], cast(null datetime2) [c5], cast(null varchar(1)) [c6], cast(null datetime2) [c7], cast(null varchar(1)) [c8], cast(null bit) [c9], cast(null int) [c10], cast(null int) [c11], [extent20].[id] [id9], ''4x0x'' [c12], [extent20].[id] [id10], [extent20].[projecttitle] [projecttitle], [extent20].[description] [description], [extent20].[createddate] [createddate], [extent20].[createdby] [createdby], [extent20].[updateddate] [updateddate], [extent20].[updatedby] [updatedby], [extent20].[isdeleted] [isdeleted], [extent20].[sequenceorder] [sequenceorder], [extent20].[employeecv_id] [employeecv_id], [extent20].[cvprofile_id] [cvprofile_id], [extent20].[associatedschedulingproject_id] [associatedschedulingproject_id], [extent20].[customer_id] [customer_id], [extent20].[employee_id] [employee_id] (select [var_50].[id] [id], [var_50].[employee_id] [employee_id] [dbo].[employeecv] [var_50] ([var_50].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent13] left outer join (select [var_51].[id] [id], [var_51].[firstname] [firstname], [var_51].[discriminator] [discriminator] [dbo].[employee] [var_51] ([var_51].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent14] on ([extent14].[discriminator] = n''employee'') , ([extent13].[employee_id] = [extent14].[id]) left outer join (select [var_52].[id] [id], [var_52].[lastname] [lastname], [var_52].[discriminator] [discriminator] [dbo].[employee] [var_52] ([var_52].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent15] on ([extent15].[discriminator] = n''employee'') , ([extent13].[employee_id] = [extent15].[id]) left outer join (select [var_53].[id] [id], [var_53].[firstname] [firstname], [var_53].[discriminator] [discriminator] [dbo].[employee] [var_53] ([var_53].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent16] on ([extent16].[discriminator] = n''employee'') , ([extent13].[employee_id] = [extent16].[id]) left outer join (select [var_54].[id] [id], [var_54].[lastname] [lastname], [var_54].[discriminator] [discriminator] [dbo].[employee] [var_54] ([var_54].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent17] on ([extent17].[discriminator] = n''employee'') , ([extent13].[employee_id] = [extent17].[id]) left outer join (select [var_55].[id] [id], [var_55].[enterpriseid] [enterpriseid], [var_55].[discriminator] [discriminator] [dbo].[employee] [var_55] ([var_55].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent18] on ([extent18].[discriminator] = n''employee'') , ([extent13].[employee_id] = [extent18].[id]) left outer join (select [var_56].[id] [id], [var_56].[level] [level], [var_56].[discriminator] [discriminator] [dbo].[employee] [var_56] ([var_56].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent19] on ([extent19].[discriminator] = n''employee'') , ([extent13].[employee_id] = [extent19].[id]) inner join (select [var_57].[id] [id], [var_57].[projecttitle] [projecttitle], [var_57].[description] [description], [var_57].[createddate] [createddate], [var_57].[createdby] [createdby], [var_57].[updateddate] [updateddate], [var_57].[updatedby] [updatedby], [var_57].[isdeleted] [isdeleted], [var_57].[sequenceorder] [sequenceorder], [var_57].[employeecv_id] [employeecv_id], [var_57].[cvprofile_id] [cvprofile_id], [var_57].[associatedschedulingproject_id] [associatedschedulingproject_id], [var_57].[customer_id] [customer_id], [var_57].[employee_id] [employee_id], [var_57].[type] [type] [dbo].[projectexperience] [var_57] ([var_57].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent20] on ([extent20].[type] = n''draft'') , ([extent13].[id] = [extent20].[employeecv_id]) (contains([extent14].[firstname], @p__linq__0)) or (contains([extent15].[lastname], @p__linq__1)) or ( exists (select 1 [c1] [dbo].[projectexperience] [extent21] (([extent21].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null)) , ([extent21].[type] = n''draft'') , ([extent13].[id] = [extent21].[employeecv_id]) , ((contains([extent21].[projecttitle], @p__linq__2)) or (contains([extent21].[description], @p__linq__3))) )) or ( exists (select 1 [c1] [dbo].[employeetags] [extent22] inner join (select [var_58].[id] [id], [var_58].[title] [title] [dbo].[tag] [var_58] ([var_58].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent23] on [extent23].[id] = [extent22].[fktagid] ([extent13].[id] = [extent22].[fkemployeecvid]) , (contains([extent23].[title], @p__linq__4)) ))) [unionall1] order [unionall1].[id] asc, [unionall1].[id1] asc, [unionall1].[id2] asc, [unionall1].[id3] asc, [unionall1].[id4] asc, [unionall1].[id5] asc, [unionall1].[id6] asc, [unionall1].[c1] asc',n'@dynamicfilterparam_isdeleted_isdeleted bit,@dynamicfilterparam_isdeleted_dynamicfilterisdisabled bit,@p__linq__0 char(4096),@p__linq__1 char(4096),@p__linq__2 char(4096),@p__linq__3 char(4096),@p__linq__4 char(4096)',@dynamicfilterparam_isdeleted_isdeleted=0,@dynamicfilterparam_isdeleted_dynamicfilterisdisabled=null,@p__linq__0='(sitecore)',@p__linq__1='(sitecore)', @p__linq__2='(sitecore)', @p__linq__3='(sitecore) ',@p__linq__4='(sitecore) ' when executing query in sql ms giving me same error. though can execute contains query directly on table this:
select * employee contains(firstname,'"john*"') this linq works:
var employeecv = _context.employees.where(x => x.firstname.contains(searchquery.keyword)).tolist();
your problem trying run fulltext not on employees table, on extent2, i.e. select employees ... , not full-text indexed. have rewrite linq query or in t-sql instead of linq.
(select [var_42].[id] [id], [var_42].[firstname] [firstname], [var_42].[discriminator] [discriminator] [dbo].[employee] [var_42] ([var_42].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted) or (@dynamicfilterparam_isdeleted_dynamicfilterisdisabled not null) ) [extent2]
i suggest try rewrite linq query this:
listofemployees = cvs in _context.employeecvs.include(x => x.employee) _context.employees.any( e=>e.firstname.contains(searchquery.keyword) && e.employeeid == cvs.employeeid )) ... etc this should generate simple exists statement should work ok. better use query syntax, because can name subqueries.
the reason why ef doing "mess" because use dynamic filters.
[var_58].[isdeleted] = @dynamicfilterparam_isdeleted_isdeleted
if try disable dynamic filters:
_context.disableallfilters(); it not now, since sets variable @dynamicfilterparam_isdeleted_dynamicfilterisdisabled in generated query, query still contain [var_xx] subqueries, because entityframework.dynamicfilters overrides methods of entity framework. see this link.
why dynamic filters cause problems?
the reason is mentioned here:
when specify additional filters on entity queries (using linq's .where() clause, example), additional filters cause ef create sub-tables in query.
so dynamic filters create subqueries , there exception, well described here. there similar workaround recommended:
the workaround i'm using (which seems work) force full text index predicate separate sub query predicate executed against base table rather intermediate result set.
so should try convert conditions using full-text indexes separate exists statements.
Comments
Post a Comment