{"id":1484,"date":"2014-04-15T21:41:16","date_gmt":"2014-04-15T20:41:16","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=1484"},"modified":"2014-04-15T21:44:51","modified_gmt":"2014-04-15T20:44:51","slug":"entity-framework-6-null-kezeles","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2014\/04\/15\/entity-framework-6-null-kezeles\/","title":{"rendered":"Entity Framework 6 null kezel\u00e9s"},"content":{"rendered":"

Ha egy nullos oszlopon sz\u0171r\u00fcnk, akkor el\u0151fordulhat, hogy a bemeneti param\u00e9ter is null.
\nwhere oszlop = @param<\/p>\n

Ebben az esetben az SQL Server alapban az ansi nulls\u00e1got alkalmazza, \u00edgy a null != nullal, azaz nem j\u00f6n vissza egy sor se. Ez\u00e9rt EF6<\/a>-ban az SQL gener\u00e1tor m\u00e1s sqlt gener\u00e1l, az ilyen esetekben egy IS NULL-os \u00e1ggal megoldja, hogy nullokra is menjen a keres\u00e9s.
\nViszont ennek dr\u00e1mai mell\u00e9khat\u00e1sai lehetnek. L\u00e1ssunk egy egyszer\u0171 p\u00e9ld\u00e1t:<\/p>\n

using (var e = new AdventureWorks2012Entities())
\n{
\n var matthew = "Matthew";
\n e.Person.FirstOrDefault(p => p.LastName == matthew);
\n}<\/p>\n

Ez esik ki az EF-b\u0151l:<\/p>\n

\r\ndeclare @p__linq__0 nvarchar(4000) = N'Matthew'\r\n\r\nSELECT TOP (1) \r\n    [Extent1].[BusinessEntityID] AS [BusinessEntityID], \r\n    [Extent1].[PersonType] AS [PersonType], \r\n    [Extent1].[NameStyle] AS [NameStyle], \r\n    [Extent1].[Title] AS [Title], \r\n    [Extent1].[FirstName] AS [FirstName], \r\n    [Extent1].[MiddleName] AS [MiddleName], \r\n    [Extent1].[LastName] AS [LastName], \r\n    [Extent1].[Suffix] AS [Suffix], \r\n    [Extent1].[EmailPromotion] AS [EmailPromotion], \r\n    [Extent1].[AdditionalContactInfo] AS [AdditionalContactInfo], \r\n    [Extent1].[Demographics] AS [Demographics], \r\n    [Extent1].[rowguid] AS [rowguid], \r\n    [Extent1].[ModifiedDate] AS [ModifiedDate]\r\n    FROM [Person].[Person] AS [Extent1]\r\n    WHERE (([Extent1].[LastName] = @p__linq__0) \r\n\tAND ( NOT ([Extent1].[LastName] IS NULL OR @p__linq__0 IS NULL))) \r\n\tOR (([Extent1].[LastName] IS NULL) AND (@p__linq__0 IS NULL))\r\n<\/pre>\n
L\u00e1that\u00f3, hogy bool algebr\u00e1val \u00f6sszehozt\u00e1k, hogy ha a param\u00e9ter null \u00e9s az oszlop is null (is nullal), akkor lej\u00f6nnek sz\u00e9pen a sorok. Viszont az ilyen query-ket ut\u00e1lja az sql server, nehezen tudja \u0151ket optimaliz\u00e1lni. option(recompile) seg\u00edtene rajta, de ezt meg nem lehet kiadni EF-en kereszt\u00fcl.<\/p>\n
Szerencs\u00e9re vissza lehet \u00e1ll\u00edtani<\/a> a r\u00e9gi k\u00f3dgener\u00e1tort is:<\/p>\n
\r\nusing (var e = new AdventureWorks2012Entities())\r\n{\r\n    e.Configuration.UseDatabaseNullSemantics = true;\r\n    var matthew = "Matthew";\r\n    e.Person.FirstOrDefault(p => p.LastName == matthew);\r\n}\r\n<\/pre>\n
Gener\u00e1lt SQL:<\/p>\n
\r\n\tSELECT TOP (1) \r\n    [Extent1].[BusinessEntityID] AS [BusinessEntityID], \r\n    [Extent1].[PersonType] AS [PersonType], \r\n    [Extent1].[NameStyle] AS [NameStyle], \r\n    [Extent1].[Title] AS [Title], \r\n    [Extent1].[FirstName] AS [FirstName], \r\n    [Extent1].[MiddleName] AS [MiddleName], \r\n    [Extent1].[LastName] AS [LastName], \r\n    [Extent1].[Suffix] AS [Suffix], \r\n    [Extent1].[EmailPromotion] AS [EmailPromotion], \r\n    [Extent1].[AdditionalContactInfo] AS [AdditionalContactInfo], \r\n    [Extent1].[Demographics] AS [Demographics], \r\n    [Extent1].[rowguid] AS [rowguid], \r\n    [Extent1].[ModifiedDate] AS [ModifiedDate]\r\n    FROM [Person].[Person] AS [Extent1]\r\n    WHERE [Extent1].[LastName] = @p__linq__0\r\n<\/pre>\n
\u00cdgy m\u00e1r egy teljesen tiszta sz\u0171r\u00e9st kapunk, de ez NEM menne null bemeneti param\u00e9terre. Ha nem is kell, hogy menjen, akkor viszont ez sokkal gyorsabb lehet, mint az els\u0151, megfelel\u0151 indexek eset\u00e9n.<\/p>\n
A k\u00e9t lek\u00e9rdez\u00e9s terv\u00e9t egym\u00e1s mell\u00e9 rakva j\u00f3l l\u00e1that\u00f3 a k\u00fcl\u00f6nbs\u00e9g:
\n\"EFUseDatabaseNullSemanticsDiff\"<\/a><\/p>\n
Nagyobb t\u00e1bl\u00e1kn\u00e1l a hat\u00e1s sokkal radik\u00e1lisabb lenne, \u00e9rdemes szem el\u0151tt tartani ezt.<\/p>\n","protected":false},"excerpt":{"rendered":"
Ha egy nullos oszlopon sz\u0171r\u00fcnk, akkor el\u0151fordulhat, hogy a bemeneti param\u00e9ter is null. where oszlop = @param Ebben az esetben az SQL…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,75,85,25,6,74,4,30],"tags":[],"class_list":["post-1484","post","type-post","status-publish","format-standard","hentry","category-net","category-net-4","category-net-45","category-adonet","category-adatbazisok","category-entity-framework","category-szakmai-elet","category-sql-server"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1484","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1484"}],"version-history":[{"count":4,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1484\/revisions"}],"predecessor-version":[{"id":1489,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1484\/revisions\/1489"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1484"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1484"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1484"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}