0
ALTER Procedure [dbo].[GetItemsTypesByProduct]
(
    @ProductID INT
)
As
Begin
    Select distinct iMap.ProductId, iTyp.Id ItemTypeId, iTyp.ItemType, iTyp.SortOrder
    from itemMapping iMap
    inner join items itm on itm.itemid = imap.itemid
    inner join itemType iTyp on iTyp.id = itm.ItemTypeId
    Where ProductId = @ProductID
    UNION
    Select distinct rMap.ProductId, iTyp.Id, iTyp.ItemType, iTyp.SortOrder
    from ReaderMapping rMap
    inner join itemType iTyp on iTyp.id = 25
    Where ProductId = @ProductID
    UNION
    Select distinct aMap.ProductId, iTyp.Id, iTyp.ItemType, iTyp.SortOrder
    from AssessmentMapping aMap
    inner join itemType iTyp on iTyp.id = 24
    Where ProductId = @ProductID
    order by productid, iTyp.SortOrder

    Select distinct aTyp.AssessmentTypeId, AssessmentTypeName, MappingLevel
    from eds_quiz..AssessmentType aTyp
    inner join eds_quiz..Assessments asm on (asm.AssessmentTypeId = aTyp.AssessmentTypeId)
    inner join AssessmentMapping aMap on (aMap.AssessmentId = asm.AssessmentId)
    Where ProductId = @ProductID

    Select Id, TypeName from eds_edusmart..lessontype
    Where Id NOT IN (1, 2, 4)
End
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
Radhika Yadhav
  • 1
  • LINQ isn't embedded SQL. It's a query language that works on top of a provider, like an ORM. If you use EF Core and specify the correct relations and inheritance model, you may be able to use just `context.Mappings.Where(m=>m.ProductID=123)` to load *all three tables*. If you – Panagiotis Kanavos Mar 09 '22 at 13:31
  • 1
    What have you tried so far? Which LINQ provider do you use? Which model do you have? – Svyatoslav Danyliv Mar 09 '22 at 14:13
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Mar 09 '22 at 20:48

0 Answers0