ืืงืืื
ะ ืขืืืขืืข ืึทืคึผืืึทืืึทืืืืฉืึทื ืืขืืืึธืืก ืืขื ืขื ืืขืืืืื LINQ ืคึฟืจืืื.
ืืึธ ืืืจ ืืืื ืคืึธืจืฉืืขืื ืขืืืขืืข ืืขืจ ืึทืคึผืจืึธืืืฉืื ืฆื ืงืึธื ืึทืคึผืืึทืืึทืืืืฉืึทื ืฉืืึทืืืช ืฆื LINQ ืคึฟืจืืื.
ืขืก ืืื ืืึทืืืืกื ืึทื LINQ(ืฉืคึผืจืึทื-ืื ืึทืืจืืืืื ืึธื ืคึฟืจืขื) ืืื ืึท ืคึผืฉืื ืืื ืืึทืงืืืขื ืฉืคึผืจืึทื ืคึฟืึทืจ ืงืืืขืจืื ื ืึท ืืึทืื ืืงืืจ.
ะ LINQ ืฆื SQL ืืื ืึท ืืขืื ืึธืืึธืืืข ืคึฟืึทืจ ืึทืงืกืขืก ืืึทืื ืืื ืึท DBMS. ืืึธืก ืืื ืึท ืฉืืึทืจืง ืืขืฆืืึทื ืคึฟืึทืจ ืืจืืขืื ืืื ืืึทืื, ืืื ืคึฟืจืืื ืืขื ืขื ืงืึทื ืกืืจืึทืงืืึทื ืืืจื ืึท ืืขืงืืึทืจืึทืืืืืข ืฉืคึผืจืึทื, ืืืึธืก ืืืขื ืืืื ืงืึธื ืืืขืจืืขื ืืื SQL ืคึฟืจืืื ืคึผืืึทืืคืึธืจืืข ืืื ืืขืฉืืงื ืฆื ืื ืืึทืืึทืืืืก ืกืขืจืืืขืจ ืคึฟืึทืจ ืืืจืืคืืจืื ื. ืืื ืืื ืืืขืจ ืคืึทื, ืืืจ ืืืื ืขื ืืืจื DBMS ืืก ืกืงื ืกืขืจืืืืจืขืจ.
ืืืขืจ, LINQ ืคึฟืจืืื ืืขื ืขื ื ืืฉื ืงืึธื ืืืขืจืืขื ืืื ืึธืคึผืืืืึทืืื ืืขืฉืจืืื ืึธื ืขืก SQL ืคึฟืจืืื, ืืืึธืก ืึท ืืงืกืคึผืืจืืึทื ืกื ืืืึท ืงืขื ืฉืจืืึทืื ืืื ืึทืืข ืื ื ืืึทื ืกืื ืคืื ืึทืคึผืืึทืืึทืืืืฉืึทื SQL ืคึฟืจืืื:
- ืึธืคึผืืืืึทื ืงืึทื ืขืงืฉืึทื ื (ืคืึทืจืืื ืื) ืืื ืคืืืืขืจ ืื ืจืขืืืืืึทืื (ืืืื)
- ืคืืืข ื ืืึทื ืกืื ืืื ื ืืฆื ืงืึทื ืขืงืฉืึทื ื ืืื ืืจืืคึผืข ืื ืึธืื
- ืคืืืข ืืืขืจืืืืฉืึทื ื ืืื ืจืืคึผืืืืกืื ื ืื ืึธืื IN ืืืืฃ ืขืงืกืืกืืะธ ื ืืฉื ืืื, <> ืืืืฃ ืขืงืกืืกืื
- ืื ืืขืจืืืืืื ืงืึทืืฉืื ื ืคืื ืจืขืืืืืึทืื ืืืจื ืฆืืึทืืืืืึทืืืง ืืืฉื, CTE, ืืืฉ ืืืขืจืืึทืืึทืื
- ื ืืฆื ืคืื ืืึทืฅ (ืึธืคึผืฆืืข) ืืื ืื ืกืืจืึทืงืฉืึทื ื ืืื ืืืฉ ืืื ืฅ ืืื (...)
- ื ืืฆื ืื ืืขืงืกื ืงืืงื ืืื ืืืื ืขืจ ืคืื ืื ืืืืืขื ืฆื ืืึทืงืืืขื ืืึทืคืจืืึทืขื ืคืื ืืืขืจืืง ืืึทืื ืจืืืื ืื ืืขืฉืึทืก ืกืึทืืขืงืฉืึทื ื
ืื ืืืืคึผื ืคืึธืจืฉืืขืืื ื ืืึทืืึทืื ืขืงืก ืคืื ืื ืจืืืึทืืืื ื SQL ืคึฟืจืืื ืืืขื ืงืึทืืคึผืืืืื ื LINQ ืคึฟืจืืื ืืขื ืขื:
- ืงืึทื ืกืึทืืึทืืืืฉืึทื ืคืื ืื ืืื ืฆืข ืืึทืื ืกืขืืขืงืฆืืข ืืขืงืึทื ืืืึทื ืืื ืืืื ืืงืฉื
- ืืืคึผืืืงืืืืื ื ืืืืขื ืืงืึทื ืืืึทืงืก ืคืื ืงืึธื, ืืืึธืก ืืขืกืึธืฃ ืคืืจื ืฆื ืงืืืคื ืืื ืืืืืง ืืึทืื ืืืืขื ืขื
- ืืจืืคึผืขืก ืคืื ืืืืื-ืงืึธืืคึผืึธื ืขื ื ืื ืึธืื (ืืึทืืืฉืืงืึทื "ืืื" ืืื "ืึธืืขืจ") - ืืื ะธ OR, ืงืึทืืืืื ืื ื ืืื ืงืึธืืคึผืืขืงืก ืื ืึธืื, ืคืืจื ืฆื ืื ืคืึทืงื ืึทื ืื ืึธืคึผืืืืืืขืจ, ืืื ืคึผืึทืกืืง ื ืื-ืงืืึทืกืืขืจื ืื ืืขืงืกืื ืคึฟืึทืจ ืื ื ืืืืืง ืคืขืืืขืจ, ืืขืกืึธืฃ ืืืืื ืฆื ืืืขืจืงืืงื ืงืขืื ืื ืงืืึทืกืืขืจื ืืื ืืขืงืก (ืืื ืืขืงืก ืืืขืจืงืืงื) ืืืื ืืจืืคึผืขืก ืคืื ืืืืื ืืื ืืขื
- ืืืฃ ื ืขืกืืื ื ืคืื ืกืึทืืงืืืขืจืื ืืืื ืคึผืึทืจืกืื ื ืืืืขืจ ืคึผืจืึธืืืขืืึทืืืง SQL ืกืืืืืืึทื ืฅ ืืื ืึทื ืึทืืืกืืก ืคืื ืื ืึธื ืคึฟืจืขื ืคึผืืึทื ืืืืฃ ืื ืืืื ืคืื ืืขืืืขืืึธืคึผืขืจืก ืืื ืืืึท
ืึธืคึผืืืืืืึทืืืึธื ืืขืืืึธืืก
ืืืฆื ืืึธืื ืก ืืึทื ืืืืื ืฆื ืึทืคึผืืึทืืึทืืืืฉืึทื ืืขืืืึธืืก.
1) ื ืึธื ืื ืืขืงืกืื ื
ืขืก ืืื ืืขืกืืขืจ ืฆื ืืึทืืจืึทืืื ืคืืืืขืจืก ืืืืฃ ืื ืืืืคึผื ืกืขืืขืงืฆืืข ืืืฉื, ืืืืึทื ืึธืคื ืื ืืื ืฆืข ืึธื ืคึฟืจืขื ืืื ืืขืืืื ืึทืจืื ืืืื ืึธืืขืจ ืฆืืืื ืืืืคึผื ืืืฉื (ืึทืคึผืคึผืืืงืึทืืืึธื ืก-ืืขื ืืฉื-ืึธืคึผืขืจืึทืืืึธื ืก) ืืื ืืื ืึท ื ืึธืจืืึทื ืืึทื ื ืคืื ืื ืึธืื (ืืกืงืืึธืกืขื, ืงืึทื ืกืึทืื, ืขื ืึทืืืขื, ืกืืึทืืืก). ืขืก ืืื ืืืืืืืง ืฆื ืฉืึทืคึฟื ืฆืื ืขืืขื ืื ืืืกืื ืคึฟืึทืจ ืื ืืืืขื ืึทืคืืื ืกืึทืืคึผืึทืื.
ืื ืืืืืื ื ืืืื ืืื ืขื ืืืขื ืกืึทืืขืงืืื ื ืื ืคืขืืืขืจ ืืืืืืืืง ืืืืึทืฅ ืื ืืืืืขืงืขืจื ืฉืืขืื ืฆื ืื ืึธื ืคึฟืจืขื.
ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืืืจ ืืึธืื 500000 ืึทืคึผืืึทืงืืืฉืึทื ื. ืึธืืขืจ, ืขืก ืืขื ืขื ืืืืื 2000 ืึทืงืืืื ืึทืคึผืืึทืงืืืฉืึทื ื. ืืขืจื ืึธื ืึท ืจืืืืืง ืืืืกืืขืงืืืื ืืื ืืขืงืก ืืืขื ืจืึทืืขืืืขื ืืื ืื ืคืื ืืื ืืขืงืก ืืืขืจืงืืงื ืืืืฃ ืึท ืืจืืืก ืืืฉ ืืื ืืืขื ืืึธืื ืืืจ ืฆื ืืขืฉืืืื ื ืืืืกืงืืืึทืื ืืึทืื ืืืจื ืึท ื ืื-ืงืืึทืกืืขืจื ืืื ืืขืงืก.
ืืืื, ืื ืคืขืื ืคืื ืื ืืขืงืกืื ืงืขื ืขื ืืืื ืืืืขื ืึทืคืืื ืืืจื ืคึผืจืึทืืคึผืก ืคึฟืึทืจ ืคึผืึทืจืกืื ื ืึธื ืคึฟืจืขื ืคึผืืึทื ื ืึธืืขืจ ืงืึทืืขืงืืื ื ืกืืกืืขื ืืืื ืื ื ืกืืึทืืืกืืืง ืืก ืกืงื ืกืขืจืืืืจืขืจ:
ืึทืืข ืืืื ืื ื ืืึทืื ืึผืืื ืืื ืคึฟืึธืจืืึทืฆืืข ืืืขืื ืคืขืื ืืืง ืื ืืขืงืกืื, ืืื ืื ืืืกื ืขื ืคืื ืกืคึผืืืฉืึทื ืื ืืขืงืกืื.
ืึธืืขืจ, ืื ืืขืงืกืื ืืื ืงืึทืืฉืื ื ืืขื ืขื ืึธืคื ืืขืืืึธืืก ืคืื ืงืึทืืืึทืืื ื ืื ืงืึทื ืกืึทืงืืืขื ืกืึทื ืคืื ืฉืืืึทื ืืขืฉืจืืื LINQ ืคึฟืจืืื ะธ SQL ืคึฟืจืืื.
ืืื ืื ืืึทืจื ืคืืจ ืคืื ืืขืื ืืืืืื, ืขืก ืืื ืึธืคื ืืืืืืืง ืคึฟืึทืจ ืึท ืืขืฉืขืคื ืฆื ืื ืกืืจืืืขื ื ืืขืฉืขืคื ืคึฟืขืึดืงืืืื ืืืจื ืืืืขืจ ืืขืืืืื ื. ืืื ืืขืจืืืขืจ, ืฉืืืขืจ ืจืืงืืืขืก ืืขื ืขื ืึธืคื ืืจืึทื ืกืคืขืจื ืฆื ืืขืจ ืืื ืืขืจืืจืื ื ืืื ืงืึทืืฉืื ื.
ืืึธืก ืืื ืืืื ืืขืจืขืืืคืืจืืืงื, ืืืืึทื ืืขืจ ืืึทื ืืฆืขืจ ืืื ื ืืฉื ืฉืืขื ืืืง ืืึทืจืคึฟื ืื ืืขืฆืืข ืืึทืื ืืื ืขืก ืืื ืึท ืคึผืึทืกืืง ืืืจืื ืคืื ืจืืกืคึผืึทื ืกืืืื ืึทืก ืคืื ืื ืืึทื ืืฆืขืจ ืฆืืืื ื.
ืืขืจ ืฆืืืึทื ื ืึทืืึทืื ืกืึทืืืืื ื ืืขืฉืขืคื ืืืืขืจืคืขื ืืฉื, ืึธืืขืจ ืืขืกืึธืฃ ืจืึทืืืกืึทื ืื ืคืึธืจืฉืืขืืื ื ืคืื ืื ืืื ืคึฟืึธืจืืึทืฆืืข ืกืืกืืขื ืืืจื ืคืฉืื ืคืึทืจืืึทืืื ืกืึทืืืฉืึทื ื ืฆื ืคึผืจืึธืืืขืืก.
ืขืก ืืื ืืืื ืืืขืจื ืืขืืขื ืงืขื ืึทื ืืื ืืขื ืคึผืจืึธืฆืขืก ืคืื ืืืื ืคึฟืึทืจ ืื ื ืืืืืง ืื ืืขืงืกืื ืฆื ืืืืื, ืคึฟืืจืืืืื MS SQL ืึทืคึผืืึทืืึทืืืืฉืึทื ืงืขื ืืืื ืคืึทืืฉ, ืึทืจืืึทื ืืขืจืขืื ื ืืื ืืขืจ ืื ืคืืืืขื ืืข ืื ืึธืื:
- ืืืื ืขืก ืืขื ืขื ืฉืืื ืื ืืขืงืกืื ืืื ืึท ืขื ืืขื ืืึทื ื ืคืื ืคืขืืืขืจ
- ืืืื ืื ืคืขืืืขืจ ืืื ืื ืืืฉ ืงืขื ืขื ื ืื ืืืื ืื ืืขืงืกื ืจืขืื ืฆื ืื ืืขืงืกืื ื ืจืืกืืจืืงืฉืึทื ื (ืืืกืงืจืืืื ืืื ืืขืจ ืืขืืึทื ).
2) ืฆืื ืืืคืืืกื ืึทืืจืืืืืฅ ืืื ืืืื ื ืืึท ืึทืืจืืืืื
ืืื ืขืืืขืืข ืคืขืืืขืจ ืคืื ืืืื ืืืฉ, ืืืึธืก ืืื ืขื ืืื ืึท ืืึทืืข ืคึฟืึทืจ ืึท ืืจืืคึผืข ืคืื โโืืืืื ืืื ืืขื, ืงืขื ืขื ืืืื ืจืืคึผืืืืกื ืืืจื ืื ืืจืึธืืืืกืื ื ืืืื ื ืืึท ืคืขืื.
ืืึธืก ืืื ืกืคึผืขืฆืืขื ืืืช ืคึฟืึทืจ ืกืืึทืืืก ืคืขืืืขืจ, ืืืึธืก ืืขื ืขื ืืืืฉืึทืืืึทืื ืึธืืขืจ ืืืกื ืึธืืขืจ ืื ืืึทืืืฉืขืจ ืืื ืืืคึผ.
ืืืึทืฉืคึผืื:
ืืกืงืืึธืกืขื = 0 ืืื ืงืึทื ืกืึทืื = 0 ืืื ืขื ืืืืึทืื = 0 ืืื ืจืืคึผืืืืกื ืืืจื ืกืืึทืืืก = 1.
ืืึธืก ืืื ืืื ืื ืื ืืึทืืืฉืขืจ ืกืืึทืืืก ืึทืืจืืืืื ืืื ืืึทืงืขื ืขื ืฆื ืขื ืฉืืจ ืึทื ืื ืกืืึทืืืกืขืก ืืขื ืขื ืคึผืึทืคึผืืึทืืืืืึทื ืืื ืื ืืืฉ. ืืขืจื ืึธื, ืืขื ื ืืึทืข ืึทืืจืืืืื ืืื ืื ืืขืงืกื.
ืืึธืก ืืื ืึท ืคืื ืืึทืืขื ืืึทื ืืืืืื ื ืฆื ืื ืคืึธืจืฉืืขืืื ื ืคึผืจืึธืืืขื, ืืืืึทื ืืืจ ืึทืงืกืขืก ืืึทืื ืึธื ืืื ืืืืืง ืืฉืืื ืืช.
3) ืืึทืืขืจืืึทืืืืืืฉืึทื ืคืื ืื ืืืื ืื ื
ืฆืื ืืึทืืืืขืจื, ืืื LINQ ืคึฟืจืืื ืฆืืืืืืืืืืืข ืืืฉื, CTEs ืืื ืืืฉ ืืืขืจืืึทืืึทืื ืงืขื ืขื ื ืื ืืืื ืืขืืืืื ื ืืืืึทื.
ืึธืืขืจ, ืขืก ืืื ืื ืื ืืขืจ ืืืขื ืฆื ืึทืคึผืืึทืืืื ืคึฟืึทืจ ืืขื ืคืึทื - ืื ืืขืงืกื ืงืืงื.
ืฆืืฉืืึทื ื ืืจืืคึผืข (ืคืื ืื ืืืืฉืคึผืื ืืืืื) ืืกืงืืึธืกืขื = 0 ืืื ืงืึทื ืกืึทืื = 0 ืืื ืขื ืืืืึทืื = 0 (ืึธืืขืจ ืึท ืกืืื ืคืื ืื ืืขืจืข ืขื ืืขื ืื ืึธืื) ืืืขืจื ืึท ืืื ืึธืคึผืฆืืข ืฆื ื ืืฆื ืืื ืืื ืึท ืื ืืขืงืกื ืืืื ืื ื, ืงืึทืืฉืื ื ืึท ืงืืืื ืจืขืคืื ืคืื ืืึทืื ืคืื ืึท ืืจืืืก ืืึทื ื.
ืึธืืขืจ ืขืก ืืขื ืขื ืึท ื ืืืขืจ ืคืื ืจืืกืืจืืงืฉืึทื ื ืืืขื ืืึทืืืจืืึทืืืื ืึท ืืืื ืื ื:
- ื ืืฆื ืคืื ืกืึทืืงืืืขืจืื, ืงืืึธืืื ืขืงืกืืกืื ืืึธื ืืืื ืจืืคึผืืืืกื ืืืจื ื ืืฆื ืคืึทืจืืื ืื
- ืืืจ ืงืขื ื ื ืืฉื ื ืืฆื ืืืฆื ืคืึทืจืืึทื ื, ืืื ืืึทื ืึทืืข, EXCEPTION, ืื ืืขืจืกืขืงื
- ืืืจ ืงืขื ืขื ื ืืฉื ื ืืฆื ืืืฉ ืืื ืฅ ืืื ืงืืึธืืื ืึธืคึผืฆืืข
- ืงืืื ืืขืืืขืืงืืื ืฆื ืึทืจืืขืื ืืื ืกืืืงืึทืื
- ืขืก ืืื ืืืืืขืืืขื ืฆื ืืืืึทืื ืืึทืื ืืื ืืืื ืืืื ืื ื ืคืื ืคืึทืจืฉืืืขื ืข ืืืฉื
ืขืก ืืื ืืืืืืืง ืฆื ืืขืืขื ืงืขื ืึทื ืื ืคืึทืงืืืฉ ื ืืฅ ืคืื ื ืืฆื ืึทื ืื ืืขืงืกื ืืืื ืื ื ืงืขื ืขื ืืืื ืึทืืฉืืืื ืืืืื ืืืจื ืึทืงืฉืื ืื ืืขืงืกืื ื ืขืก.
ืึธืืขืจ ืืืขื ืืืจ ืจืืคื ืึท ืืืื ืื ื, ืื ืื ืืขืงืกืื ืงืขื ื ืืฉื ืืืื ืืขืืืืื ื, ืืื ืฆื ื ืืฆื ืืื ืืคืืจืืฉ, ืืืจ ืืืื ืกืคึผืขืฆืืคืืฆืืจื ืืื (ื ืึธืขืงืกืคึผืึทื ื).
ืืื ื ืืื LINQ ืคึฟืจืืื ืขืก ืืื ืืืืืขืืืขื ืฆื ืืขืคืื ืืจื ืืืฉ ืืื ืฅ, ืึทืืื ืืืจ ืืึธืื ืฆื ืฉืึทืคึฟื ืื ืื ืืขืจ ืคืึทืจืืจืขืืื ื - ืึท "ืจืึทืคึผืขืจ" ืคืื ืื ืคืืืืขื ืืข ืคืึธืจืขื:
CREATE VIEW ะะะฏ_ะฟัะตะดััะฐะฒะปะตะฝะธั AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);
4) ื ืืฆื ืืืฉ ืคืึทื ืืงืฉืึทื ื
ืึธืคื ืืื LINQ ืคึฟืจืืื ืืจืืืก ืืืึทืงืก ืคืื ืกืึทืืงืืืขืจืื ืึธืืขืจ ืืืึทืงืก ื ืืฆื ืงืืงื ืืื ืึท ืงืึธืืคึผืืขืงืก ืกืืจืืงืืืจ ืคืึธืจืขื ืึท ืืขืฆื ืึธื ืคึฟืจืขื ืืื ืึท ืืืืขืจ ืงืึธืืคึผืืืฆืืจื ืืื ืกืืืึธืคึผืืืืึทื ืืืจืืคืืจืื ื ืกืืจืืงืืืจ.
ืฉืืืกื ืืขื ืขืคืืฅ ืคืื ื ืืฆื ืืืฉ ืคืึทื ืืงืฉืึทื ื ืืื LINQ ืคึฟืจืืื:
- ืื ืคืืืืงืืื, ืืื ืืื ืื ืคืึทื ืคืื ืงืืงื, ืฆื ืืืื ืืขืืืืื ื ืืื ืกืคึผืขืกืืคืืขื ืืื ืึท ืืืืคืขืฅ, ืึธืืขืจ ืืืจ ืงืขื ืขื ืคืึธืจื ืึท ืกืืื ืคืื ืึทืจืืึทื ืฉืจืืึทื ืคึผืึทืจืึทืืขืืขืจืก:
ืคึฟืื ืคึฟืื ืงืฆืืข (@param1, @param2 ...)
ืืื ืึท ืจืขืืืืืึทื, ืคืืขืงืกืึทืืึทื ืืึทืื ืืืกืืขืจืื ื ืงืขื ืขื ืืืื ืึทืืฉืืืื - ืืื ืืขื ืคืึทื ืคืื ื ืืฆื ืึท ืืืฉ ืคึฟืื ืงืฆืืข, ืขืก ืืขื ืขื ื ืื ืึทืืึท ืฉืืึทืจืง ืจืืกืืจืืงืฉืึทื ื ืืื ืืื ืื ืคืึทื ืคืื ืื ืืขืงืกื ืงืืงื ืืืกืงืจืืืื ืืืืื:
- ืืึทืืืข ืขืฆืืช:
ืืืจื LINQ ืืืจ ืงืขื ื ื ืืฉื ืกืคึผืขืฆืืคืืฆืืจื ืืืึธืก ืื ืืขืงืกืื ืืึธื ืืืื ืืขืืืืื ื ืืื ืืึทืฉืืืืขื ืื ืืึทืื ืืคืืขืืื ืืขืจืืงืืื ืืืจืื ืืืขื ืืืจ ืคืจืขืื.
ืืืขืจ ืื ืคืื ืงืฆืืข ืืื ืื ืงืืืคึผืึทืืืืึทืืื.
ืืื ืื ืคืื ืงืฆืืข, ืืืจ ืงืขื ืขื ืืขืจืืจืืืื ืึท ืืึทื ืฅ ืงืขืกืืืืขืจืืืง ืืืจืืคืืจืื ื ืึธื ืคึฟืจืขื ืคึผืืึทื, ืืื ืึผืืืื ืคึฟืึทืจ ืืจืืขืื ืืื ืื ืืขืงืกืื ืืื ืืึทืื ืืคืืขืืื ืืขืจืืงืืื ืืขืืืขืืก ืืขื ืขื ืืืคืืื ื. - ื ืืฆื ืื ืคึฟืื ืงืฆืืข, ืืื ืคืึทืจืืืืึทื ืืื ืื ืืขืงืกื ืงืืงื, ืฆื ืืึทืงืืืขื:
- ืงืึธืืคึผืืขืงืก ืืึทืื ืืืกืืขืจืื ื ืืึธืืืง (ืืคืืื ื ืืฆื ืืืคึผืก)
- ืืึทืงืืืขื ืืึทืื ืคืื ืคืืืข ืคืึทืจืฉืืืขื ืข ืืืฉื
- ื ืืฆื ืคืึทืจืืึทื ื ะธ ืขืงืกืืกืื
- ืืึทืืืข ืขืฆืืช:
- ืคืึธืจืฉืืึธื ืึธืคึผืฆืืข ืืืืขืจ ื ืืฆืืง ืืืขื ืืืจ ืืึทืจืคึฟื ืฆื ืฆืืฉืืขืื ืงืึทื ืงืขืจืึทื ืกื ืงืึธื ืืจืึธื OPTION(MAXDOP N), ืืขืจ ืกืืจ ืคืื ืื ืึธื ืคึฟืจืขื ืืืจืืคืืจืื ื ืคึผืืึทื. ืืืฉื:
- ืืืจ ืงืขื ืขื ืกืคึผืขืฆืืคืืฆืืจื ืึท ืืขืฆืืืื ืืขื ืฉืืึทืขื-ืฉืึทืคืื ื ืคืื ืื ืึธื ืคึฟืจืขื ืคึผืืึทื ืึธืคึผืฆืืข (ืจืืงืึธืืคึผืืืข)
- ืืืจ ืงืขื ืขื ืกืคึผืขืฆืืคืืฆืืจื ืฆื ืฆื ืฆืืืื ืืขื ืื ืึธื ืคึฟืจืขื ืคึผืืึทื ืฆื ื ืืฆื ืื ืคืึทืจืืื ืื ืกืืจ ืกืคึผืขืกืืคืืขื ืืื ืื ืึธื ืคึฟืจืขื ืึธืคึผืฆืืข (ืงืจืึทืคื ืกืืจ)
ืืขืจ ืคืจืืื ืืืขืื ืึธืคึผืฆืืข ืืืกืงืจืืืื .
- ื ืืฆื ืื ืฉืืึธืืึทืกื ืืื ืืขืจืกื ืคืืจืืื ืื ืืึทืื ืจืขืคืื:
ืขืก ืืื ื ืื ืืึทืจืคึฟื ืฆื ืงืจืึธื ืืจืืืก ืืึทืื ืฉืืขืื ืืื ืงืึทืืฉืขืก (ืืื ืืื ืืขืจ ืคืึทื ืืื ืื ืืขืงืกื ืงืืงื), ืคึฟืื ืืืึธืก ืืืจ ื ืึธื ืืึทืจืคึฟื ืฆื ืคืืืืขืจ ืื ืืึทืื ืืืจื ืคึผืึทืจืึทืืขืืขืจ.
ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืขืก ืืื ืึท ืืืฉ ืืืขืืขื ืก ืคืืืืขืจ ืืืื ืืจืื ืคืขืืืขืจ ืืืขืจื ืืขื ืืฆื (ืึท, ื, ื).ืงืึทื ืืืขื ืฉืึทื ืึทืื, ืึทืืข ืจืืงืืืขืก ืืึธืื ืึท ืงืขืกืืืืขืจืืืง ืฆืืฉืืึทื ื ืึท = 0 ืืื ื = 0.
ืึธืืขืจ, ืื ืืขืื ืคึฟืึทืจ ืื ืคืขืื c ืืขืจ ืืืขืจืืึทืืึทื.
ืืื ืื ืฆืืฉืืึทื ื ืึท = 0 ืืื ื = 0 ืขืก ืืึทืงืข ืืขืืคึผืก ืืื ืื ืฆื ืืึทืืจืขื ืขืฆื ืื ืคืืจืืื ืื ืจืืืึทืืืื ื ืฉืืขืื ืฆื ืืืืื ืืขืจ ืคืื ืจืขืงืึธืจืืก, ืึธืืขืจ ืื ืฆืืฉืืึทื ื ืืืืฃ ั ื ืขืจืึธืื ืื ืกืขืืขืงืฆืืข ืึทืจืึธืคึผ ืฆื ืึท ืืื ืืขืจื ืจืขืงืึธืจืืก.
ืืึธ ืื ืืืฉ ืคึฟืื ืงืฆืืข ืงืขื ืืืื ืึท ืืขืกืขืจ ืึธืคึผืฆืืข.
ืืืื, ืึท ืืืฉ ืคึฟืื ืงืฆืืข ืืื ืืขืจ ืคึผืจืืืืงืืึทืืึทื ืืื ืงืึธื ืกืืกืืขื ื ืืื ืืืจืืคืืจืื ื ืฆืืื.
ืขืงืกืึทืืคึผืืขืก
ืืื ืก ืงืืง ืืื ืึท ืืืืฉืคึผืื ืืืคึผืืึทืืขื ืืืืฉืึทื ื ืืฆื ืื ืคึฟืจืืื ืืึทืืึทืืืืก ืืื ืึท ืืืืฉืคึผืื.
ืขืก ืืื ืึท ืืงืฉื ืืืืกืงืืืึทืื, ืืืึธืก ืงืึทืืืืื ื ืขืืืขืืข ืืืฉื ืืื ื ืืฆื ืืืื ืืืื ืื ื (OperativeQuestions), ืืื ืืืึธืก ืึทืคืืืืืืฉืึทื ืืื ืึธืคึผืืขืฉืืขืื ืืืจื E- ืืจืืื (ืืืจื ืขืงืกืืกืื) ืฆื "ืึทืงืืืืืข ืคึฟืจืืื" ([ืึธืคึผืขืจืึทืืืืืข ืคึฟืจืืื]):
ืคืืจืืื ื ื ืืืขืจ 1
(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4]
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL)
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));
ืื ืืืื ืื ื ืืื ืึท ืืึทื ืฅ ืงืึธืืคึผืืขืงืก ืกืืจืืงืืืจ: ืขืก ืืื ืกืึทืืงืืืขืจื ืืืฉืืื ืฅ ืืื ื ืืฆื ืกืึธืจืืื ื ืืืืืขื, ืืืึธืก ืืื ืึทืืืขืืืื ืืื ืึท ืคืขืจืื ืืืื-ืืื ืืขื ืกืืืืข ืึธืคึผืขืจืึทืฆืืข.
ื ืืืกืืขืจ ืคืื OperativeQuestions ืืื ืืืขืื ืฆืขื ืืืืื ื ืจืขืงืึธืจืืก.
ืืขืจ ืืืืคึผื ืคึผืจืึธืืืขื ืืื ืืขื ืึธื ืคึฟืจืขื ืืื ืึทื ืคึฟืึทืจ ืื ืจืขืงืึธืจืืก ืคืื ืื ืืืกืืืืื ืืงืกื ืึธื ืคึฟืจืขื, ืึทื ืื ืขืจืืขื ืกืึทืืงืืืขืจื ืืื ืขืงืกืึทืงืืืืึทื ืืืืฃ ืื [ืึธืคึผืขืจืึทืืืืืข ืคึฟืจืืื] ืืืื ืื ื, ืืืึธืก ืืึธื ืคึฟืึทืจ [Email] = @p__linq__0 ืืึธืื ืืื ืื ืฆื ืืึทืืจืขื ืขืฆื ืื ืคึผืจืึธืืืงืฆืืข ืกืขืืขืงืฆืืข (ืืืจื. ืขืงืกืืกืื) ืืื ืืื ืืขืจืืขืจ ืจืขืงืืจืืก.
ืืื ืขืก ืงืขื ืืืกืงืืืขื ืึทื ืื ืกืึทืืงืืืขืจื ืืึธื ืจืขืืขื ืขื ืื ืจืขืงืึธืจืืก ืึทืืึธื ืืืจื [Email] = @p__linq__0, ืืื ืืึทื ืื ืคึผืึธืจ ืคืื ืืื ืืขืจื ืจืขืงืึธืจืืก ืืึธื ืืืื ืงืึธื ื ืขืงืืขื ืืืจื ID ืืื ืคึฟืจืืื, ืืื ืื ืึธื ืคึฟืจืขื ืืืขื ืืืื ืฉื ืขื.
ืืื ืคืึทืงื, ืขืก ืืื ืึท ืกืึทืงืืืขื ืืฉืึทื ืคึฟืึทืจืืื ืืื ื ืคืื ืึทืืข ืืืฉื: ืงืึธื ืืจืึธืืืจื ืื ืงืึธืจืขืกืคึผืึธื ืืขื ืฅ ืคืื ืฉืืึทื ืคึฟืจืืื ืืื ืฉืืึทื ืคึฟืื ืึธืคึผืขืจืึทืืืืืข ืคึฟืจืืื ืืื ืคึฟืืืืจืืจืื ื ืืืจื E- ืืจืืื.
ืืื ืคืึทืงื, ืื ืืขืื ืึทืจืืขื ืืื ืึทืืข ืืขื ืก ืคืื ืืืืื ืืขืจ ืคืื ืึธืคึผืขืจืึทืืืืืข ืคึฟืจืืื ืจืขืงืึธืจืืก, ืึธืืขืจ ืืืืื ืื ืืึทืื ืคืื ืืื ืืขืจืขืก ืืื ืืืจืฃ ืืืจื E- ืืจืืื.
ืึธืคึผืขืจืึทืืืืืข ืคึฟืจืืื ืงืืง ืืขืงืกื:
ืคืืจืืื ื ื ืืืขืจ 2
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM [dbo].Questions AS Q INNER JOIN
[dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id
OUTER APPLY
(SELECT 1 AS HasNoObjects
WHERE NOT EXISTS
(SELECT 1
FROM [dbo].ObjectUserAccesses AS BOU
WHERE BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
[dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL
AND (BO.HasNoObjects = 1 OR
EXISTS (SELECT 1
FROM [dbo].ObjectUserAccesses AS BOU INNER JOIN
[dbo].ObjectQuestions AS QBO
ON QBO.[Object_Id] =BOU.ObjectId
WHERE BOU.ProcessUserAccessId = BPU.Id
AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));
ืขืจืฉื ืืืื ืื ื ืืึทืคึผืื ื ืืื DbContext (EF Core 2)
public class QuestionsDbContext : DbContext
{
//...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
//...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
ืขืจืฉื LINQ ืึธื ืคึฟืจืขื
var businessObjectsData = await context
.OperativeQuestions
.Where(x => x.Email == Email)
.Include(x => x.Question)
.Select(x => x.Question)
.SelectMany(x => x.ObjectQuestions,
(x, bo) => new
{
Id = x.Id,
ObjectId = bo.Object.Id,
ObjectTypeId = bo.Object.ObjectType.Id,
ObjectTypeName = bo.Object.ObjectType.Name,
ObjectExternalId = bo.Object.ExternalId
})
.ToListAsync();
ืืื ืืขื ืืึทืืื ืืขืจ ืคืึทื, ืืืจ ืืึทืืจืึทืืื ืึท ืืืืืื ื ืฆื ืืขื ืคึผืจืึธืืืขื ืึธื ืื ืคืจืึทืกืืจืึทืงืืฉืขืจืึทื ืขื ืืขืจืื ืืขื, ืึธื ืื ืืจืึธืืืืกืื ื ืึท ืืึทืืื ืืขืจ ืืืฉ ืืื ืคืึทืจืืืง ืจืขืืืืืืื ("ืึทืงืืืืืข ืคึฟืจืืื"), ืืืึธืก ืืืึธืื ืืึทืจืคื ืึท ืืขืงืึทื ืืืึทื ืฆื ืคึผืืึธืืืืจื ืขืก ืืื ืืึทืื ืืื ืืึทืืื ืขืก ืึทืจืืืฃ-ืฆื-ืืึธื. .
ืืึธืืฉ ืืึธืก ืืื ืึท ืืื ืืืืืื ื, ืขืก ืืื ืื ืื ืืขืจ ืึธืคึผืฆืืข ืฆื ืึทืคึผืืึทืืืื ืืขื ืคึผืจืึธืืืขื.
ืืขืจ ืืืืคึผื ืฆืื ืืื ืฆื ืงืึทืฉ ืืืื ืกื ืืืจื [Email] = @p__linq__0 ืคึฟืื ืื ืึธืคึผืขืจืึทืืืืืข ืคึฟืจืืื ืืืื ืื ื.
ืืึทืงืขื ืขื ืื ืืืฉ ืคึฟืื ืงืฆืืข [ืืืึธ].[OperativeQuestionsUserMail] ืืื ืื ืืึทืืึทืืืืก.
ืืืจื ืฉืืงื E- ืืจืืื ืืื ืึท ืึทืจืืึทื ืฉืจืืึทื ืคึผืึทืจืึทืืขืืขืจ, ืืืจ ืืึทืงืืืขื ืฆืืจืืง ืึท ืืืฉ ืคืื ืืืึทืืืขืก:
ืคืืจืืื ื ื ืืืขืจ 3
CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
@Email nvarchar(4000)
)
RETURNS
@tbl TABLE
(
[Id] uniqueidentifier,
[Email] nvarchar(4000)
)
AS
BEGIN
INSERT INTO @tbl ([Id], [Email])
SELECT Id, @Email
FROM [OperativeQuestions] AS [x] WHERE [x].[Email] = @Email;
RETURN;
END
ืืึธืก ืงืขืจื ืึท ืืึทืืืข ืคืื โโืืืึทืืืขืก ืืื ืึท ืคึผืจืขืืขืคืื ืขื ืืึทืื ืกืืจืืงืืืจ.
ืึผืื ืื ืคึฟืจืืื ืฆื OperativeQuestionsUserMail ืืึธื ืืืื ืึธืคึผืืืืึทื ืืื ืืึธืื ืึธืคึผืืืืึทื ืึธื ืคึฟืจืขื ืคึผืืึทื ื, ืึท ืฉืืจืขื ื ืกืืจืืงืืืจ ืืื ืคืืจืืื ืื, ืืื ื ืื ืจืขืืืจื ืก ืืืฉ ืืื ืฆืืจืืงืงืืืขื...
ืืื ืืขื ืคืึทื, ืื ืคืืจืืื ืื ืึธื ืคึฟืจืขื 1 ืืื ืงืึธื ืืืขืจืืขื ืืื ืึธื ืคึฟืจืขื 4:
ืคืืจืืื ื ื ืืืขืจ 4
(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4]
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2]
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);
ืืึทืคึผืื ื ืงืืงื ืืื ืคืึทื ืืงืฉืึทื ื ืืื DbContext (EF Core 2)
public class QuestionsDbContext : DbContext
{
//...
public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
//...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
}
}
public static class FromSqlQueries
{
public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
=> source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}
ืืขืฆื LINQ ืึธื ืคึฟืจืขื
var businessObjectsData = await context
.OperativeQuestions
.GetByUserEmail(Email)
.Include(x => x.Question)
.Select(x => x.Question)
.SelectMany(x => x.ObjectQuestions,
(x, bo) => new
{
Id = x.Id,
ObjectId = bo.Object.Id,
ObjectTypeId = bo.Object.ObjectType.Id,
ObjectTypeName = bo.Object.ObjectType.Name,
ObjectExternalId = bo.Object.ExternalId
})
.ToListAsync();
ืืขืจ ืกืืจ ืคืื ืืืจืืคืืจืื ื ืฆืืื ืืื ืืขืคืืื ืคืื 200-800 ืืื, ืฆื 2-20 ืืื, ืืื"ื ื, ื"ื ืืขื ืก ืคืื ืืื ืคืึทืกืืขืจ.
ืืืื ืืืจ ื ืขืืขื ืขืก ืืขืจ ืืืจืืฉื ืืืืขื, ืึทื ืฉืืึธื ืคืื 350 ืืื, ืืืจ ืืึทืงืืืขื 8 ืืื.
ืคืื ืื ืงืืึธืจ ืืื ืืขืจ ืืึธื ืึทืืืืึทื ืืืืืฉืื ืืืจ ืืืื ืืึทืงืืืขื:
- ืึทืืืขืืืื ืจืขืืืงืฆืืข ืืื ืืืืขื ืขื ืืึทืกืข,
- ืืึทืืืึทืืืง ืจืขืืืงืฆืืข ืืื ืื ืืืงืขืืืืึธืึธื ืคืื ืืืึทืงืื ื
- ืจืืืืกืื ื ืื ืืืจืืฉื ืืืืขื ืืืึทืงืื ื ืฆืืื ืฆื ืคึผืึทืกืืง ืืืึทืืืขืก
ืจืขืืืืืึทื
ืึธืคึผืืืืืืึทืืืึธื ืืื ืคืืื-ืืื ืื ื ืคืื ืืืืืึทืืืืก ืงืึทืืืก MS SQL ืืืจื LINQ ืืื ืึท ืคึผืจืึธืืืขื ืืืึธืก ืงืขื ืขื ืืืื ืกืึทืืืื.
ืึทืืขื ืืืืื ืึทืก ืืื ืงืึธื ืกืืกืืขื ืกื ืืขื ืขื ืืืืขืจ ืืืืืืืง ืืื ืืขื ืึทืจืืขื.
ืืื ืื ืึธื ืืืื ืคืื ืืขื ืคึผืจืึธืฆืขืก:
- ืขืก ืืื ื ืืืืืง ืฆื ืงืึธื ืืจืึธืืืจื ืื ืืึทืื ืืื ืืืึธืก ืื ืืขืื ืึทืจืืขื (ืืืึทืืืขืก, ืืืืกืืขืงืืืื ืืึทืื ืืืืคึผืก)
- ืืืจืืคืืจื ืืขืืขืจืืง ืื ืืขืงืกืื ื ืคืื ืื ืืึทืื
- ืงืึธื ืืจืึธืืืจื ืื ืงืขืจืขืงืื ืึทืก ืคืื ืืืฉืืื ืื ื ืื ืึธืื ืฆืืืืฉื ืืืฉื
ืืขืจ ืืืืึทืืขืจ ืึทืคึผืืึทืืึทืืืืฉืึทื ืืืขืจืึทืืืึธื ืจืืืืืื:
- ืืงืขืจ ืคืื ืื ืืขืื ืืื ืืืคืืื ื ืื ืืืืคึผื ืืขืื ืคืืืืขืจ
- ืจืืคึผืืืื ื ืขื ืืขื ืึธื ืคึฟืจืขื ืืืึทืงืก ืืื ืึทื ืึทืืืืืื ื ืื ืื ืืขืจืกืขืงืฉืึทื ืคืื ืืืืื ืืื ืืขื
- ืืื SSMS ืึธืืขืจ ืื ืืขืจืข GUI ืคึฟืึทืจ ืกืงื ืกืขืจืืืืจืขืจ ืึธืคึผืืืืืืขืก ืืื SQL ืึธื ืคึฟืจืขื (ืึทืืึธืงืึทืืื ื ืึท ืื ืืขืจืืืืืื ืืึทืื ืกืืึธืจืืืืฉ, ืืืืขื ืื ืจืืืึทืืืื ื ืึธื ืคึฟืจืขื ื ืืฆื ืืขื ืกืืึธืจืืืืฉ (ืขืก ืงืขื ืืืื ืขืืืขืืข))
- ืืื ืื ืืขืฆืืข ืืื ืข, ืืขื ืืืขื ืืื ืึท ืืงืขืจ ืื ืจืืืึทืืืื ื SQL ืึธื ืคึฟืจืขื, ืื ืกืืจืืงืืืจ ืืื ืจืืืืื LINQ ืึธื ืคึฟืจืขื
ืื ืจืืืึทืืืื ื LINQ ืึธื ืคึฟืจืขื ืืึธื ืืืขืจื ืืืืขื ืืงืึทื ืืื ืกืืจืืงืืืจ ืฆื ืื ืืืืขื ืึทืคืืื ืึธืคึผืืืืึทื SQL ืึธื ืคึฟืจืขื ืคืื ืคืื ื 3.
ืึทืงืงื ืึธืืืืขืืืืขื ืฅ
ื ืืจืืืกื ืืื ืง ืคืืจ ืืืจืื ะธ ืคืื ืื ืคืืจืืข ืคืึธืจืืืก ืคึฟืึทืจ ืืืืฃ ืืื ืคึผืจืืคึผืขืจืื ื ืืขื ืืึทืืขืจืืึทื.
ืืงืืจ: www.habr.com