Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
do sql server table relationships count as joins in Linq RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    In this mvc project I inherited there is an entity with several tables and relationships as the image here shows. There is a linq statement I am trying to unravel. The statement and question below the image:

    diagram of edmx entity

    Here is the linq statement which is based on these tables

    var apps = db.vDeptAppDtls.Where(a => !string.IsNullOrEmpty(a.DeptHL) 
              && (a.AppCI.DispositionID != null && a.AppCI.AppDisposition.Disposition != "Retire") 
              && !(a.AppCI.Deleted.HasValue && a.AppCI.Deleted.Value))
              .Select(s => new { Dept = s.DeptHL, AppCIID = s.AppCIID }).Distinct().ToList();

    vDeptAppDtls is a table, AppCI is a table,  AppDisposition is also a table -- which this is where my confusion is lying -- && a.AppCI.AppDisposition.Disposition ... 

    are these tables being implicitly joined by the periods . ?

    DeptHL is a field in vDeptAppDtls  and the FK relationship column is AppCIID between the two tables (the relationship in the sql server DB).  The context is  db. 

    So based on this information -- does the Linq statement above here indicate that this is a join statement between these two tables?

    Rich P



    Wednesday, October 4, 2017 9:44 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    In more detail of my question --in  the Linq query above

    apps.Count  returns 2763 records (for my dataset).

    In my T sql equivalent

    select distinct t1.depthl, t1.AppCIID  from (vDeptAppDtl t1 join AppCI t2 on t1.AppCIID = t2.AppCIID) 
                      join  AppDisposition t3 on t2.DispositionID = t3.AppDispositionID
where t1.DeptHL is not null and t2.DispositionID is not null and t3.Disposition != 'Retire'
and t2.Deleted is not null    --and t2.Deleted = 1

    This is returning 2769 rows.  If I include the commented out --and t2.Deleted = 1  --(or 0)

    I get either 288 rows or 2541 rows (for 0 value).

    So my T sql interpretations of the Linq Query is not accurate.  Could someone see what a more correct T sql interpretation of the Linq Query would be so that I get the same number of rows from the app and from Query Analyzer?

    Rich P

    Wednesday, October 4, 2017 11:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote


    Hi Rich P123,

    >>In this mvc project I inherited there is an entity with several tables and relationships as the image here shows. There is a linq statement I am trying to unravel. The statement and question below the image:

    thanks for posting here.

    For your case about ASP.NET MVC, please go to ASP.NET MVC forum for getting suitable help.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools . If you have some grammar or code errors, please feel free to contact us.

    Your understanding and cooperation will be grateful.

    Best Regards,

    Yohann Lu

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 5, 2017 5:13 AM