4

Comparing a guid value from c# with a unique identifier column in database via linq to sql. But in created sql statement it casts my guid value into uniqueidentifier which i dont think is necessary.

How can i prevent linq creating such casts?

Here is my linq to sql query in c#;

var q = (from c in db.Contents
                     where contentIds.Contains(c.ContentId)
                     select new { c.ContentId, c.ContentTypeId }).ToList();

contentIds is a List< Guid >, and c.ContentId column type is uniqueidentifier and i am trying to get the information about the contents given in the list contentIds.

And the query created i get from sql profiler;

SELECT 
    [Extent1].[ContentTypeId] AS [ContentTypeId], 
    [Extent1].[ContentId] AS [ContentId]
    FROM [dbo].[Contents] AS [Extent1]
    WHERE [Extent1].[ContentId] IN (cast('63373d5e-7c1a-f0c8-1e82-08d12cf88503' as uniqueidentifier), cast('16cf392a-0079-5dc1-5b00-08d12d12a516' as uniqueidentifier), cast('98aed2e3-6fd4-84c5-5edb-08d134e72780' as uniqueidentifier), cast('3168c33f-33e4-3bcf-18f1-08d134e72851' as uniqueidentifier), cast('7f07e4cf-01f2-e6c1-8bb2-08d13a3d3288' as uniqueidentifier), cast('6ba478ce-7622-26c0-f607-08d13a3d952b' as uniqueidentifier), cast('46a73f7b-cb5e-c6c1-ea58-08d13a3da1e5' as uniqueidentifier), cast('13280803-4e17-3fcd-bef1-08d13a3da21a' as uniqueidentifier), cast('09f20fda-e301-e9c1-bd61-08d13a3da2e6' as uniqueidentifier), cast('b519477c-61b1-cec1-bacc-08d13a3da24f' as uniqueidentifier), cast('d30dc289-69f6-aace-747b-08d13a3da292' as uniqueidentifier), cast('0811be9d-ea0e-00c1-b9f3-08d13a3da2b5' as uniqueidentifier), cast('322baafd-40dc-cbcc-83ca-08d13a3da304' as uniqueidentifier))

Bonus, if there is a way to get rid of the SQL "IN" statement here, i would be happy to learn.

Azadrum
  • 756
  • 6
  • 23
  • FYI, the IN statement comes from you using a collection (contentIds) with Contains. This is how EF and Linq to SQL query for multiple matches. The only other way around it would be to build a dynamic query with a bunch of ORs which I'm not sure is preferrable... – Dismissile May 20 '14 at 14:45
  • also if I am not mistaken when you are selecting `new` behind the scenes Table schema in EF so since it's being cast as a Unique Identifier i.e GUID this may yield the results perhaps that Content Id should be defined as an Auto Increment field – MethodMan May 20 '14 at 14:47
  • well @Dismissile i have found that i cannot get rid of in statement here but there is a way to increase performance via using stored procedures, here (http://stackoverflow.com/a/23660446/2013934) check the answer i get to my other question. – Azadrum May 20 '14 at 14:55
  • @DJKRAZE cast is put into the where statement it's not casting the results – Azadrum May 20 '14 at 14:55
  • I agree that in TSQL, this cast is not needed: `WHERE field IN (cast('2fe0016e-799c-42c8-b8fe-9e3aff7082f3' as uniqueidentifier))` – Glen Little Feb 25 '15 at 22:15

0 Answers0