locked
Entity Framework Navigation Properties(/association) with condition RRS feed

  • Question

  • Hello,

    i've been searching on the forum but i couldnt find the answer to the question i have regarding navigation properties. I like using them but i have hit a brick wall.

    In sql i would do the following:

    SEL X.REPORTDATE, X.ID, Y.MEASUREMENT
    FROM CAR as X
    LEFT JOIN PERSON as Y ON
    X.REPORTDATE > Y.BEGIN_DATE and
    X.REPORTDATE < Y.END_DATE and
    X.ID = Y.ID

    I would like to be able to able to use navigation properties and write in c#:

    context.CAR.PERSON.ID

    I found no way to include conditions like that in the association or the navigation property while it would seem to me that this is a standard situation where you need more then the equality operator. i can't find a way to create such a association and navigation property. Can someone help me with this?






    • Edited by chimeister Wednesday, January 23, 2013 9:12 AM
    Wednesday, January 23, 2013 8:53 AM

All replies

  • Hi Chimeister,

    Welcome to the MSDN forum.

    I think you can use linq to entities like this:

    var query = from x in context.CAR
                        from y in x.Person
                        where x.REPORTDATE > y.BEGIN_DATE && x.REPORTDATE < y.END_DATE && x.ID = y.ID
                       select new { Car = x, Person = y };

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 24, 2013 6:54 AM
  • Thanx for the answer alexander but its not exactly what is was looking for. I know its possible through a linq query. But my database and tables are actually more complex. I was looking for a way so i dont have to keep writing to all the 'where' parts. The user (other programmers) of the entity diagram would need to know on which tables to include the where parts and which he could use navigation. I dont find this very transparent. I would think since these part dont change (beause the join statement is always the same), i wouldnt expect to have to repeat the statement every time.

    I always thought the idea of using the entity framework would be that you only specify the associations once and then you could navigate through it using navigation properties.

    But if such a solution doesnt exist that would mean that Navigation would only support the equals operator and no other.




    • Edited by chimeister Thursday, January 24, 2013 11:05 AM
    Thursday, January 24, 2013 9:37 AM
  • Hi Chimeister,

    Welcome to the MSDN forum.

    If you enable lazy loading, the navigation properties will be loaded automatically. For your requirement, I recommend you turn off lazy loading. You could turn off lazy loading via this: context.Configuration.LazyLoadingEnabled = false;

    var query = from x in context.CAR
                        let p = (from y in x.Person
                                     where x.REPORTDATE > y.BEGIN_DATE && x.REPORTDATE < y.END_DATE && x.ID = y.ID
                                     select y)
                        select new { Car = x, Person = p };

    Then the result will only contain the navigation properties which meet the condition. You can manipulate the result without filter the navigation property every time.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.




    Tuesday, January 29, 2013 3:03 AM
  • Hello Alexander,

    i dont think i made the question clear.  So i will start from the beginning

    Lets say i have two tables MORTGAGE and SALESGUY. I dont have Foreign key relationship.

    MORTGAGE
    - ID  (PRIMARY KEY)
    - REPORTDATE (PRIMARY KEY)
    - SALESGUY_ID (PRIMARY KEY)

    SALESGUY
    - ID (PRIMARY KEY)
    - DESCRIPTION
    - BEGIN_DATE (PRIMARY KEY)
    - END_DATE (PRIMARY KEY)

    In this example table SALESGUY has a DATE_BEGIN field and a DATE_END field. The SALESGUY description can change over time meaning the same ID can be reused by a different salesguy. To support history of this we have these two fields.

    The case is that now i want to know who sold all the mortgages on a specific date. If i didnt know about navigation properties i would do it like you suggested.

    But i would like to use it like below

    .. 
    where (Mortgage.Repordate == '2012-12-31' && Mortgage.SALESGUYID == 3) select new { Mortgage.ID , Mortgage.SalesGuy.DESCRIPTION }

    but i cannot do this because i cant create a conditional association for the dates because they require for me to specify a >= operator and  <= operator.

    What i'm trying to do i to specify the relationship in the EDMX file? so i don't have to worry about the relationship. The relationship between Mortgage and SalesGuy doesnt' change.







    • Edited by chimeister Tuesday, January 29, 2013 1:23 PM
    Tuesday, January 29, 2013 1:19 PM
  • Hi Chimeister,

    Welcome to the MSDN forum.

    I guess you are using database first, so I don’t recommend you modify database via edmx. I am not sure if this is what you need. Please check this:

                    var query = (from m in context.MORTGAGES
                                 join s in context.SALESGUIES on m.SALESGUY_ID equals s.ID into gj
                                 from sub in gj.DefaultIfEmpty()
                                 select new { M = m, S = sub }).ToList();
    
                    var query2 = (from r in query
                                  where (r.M.REPORTDATE == '2012-12-31' && r.M.SALESGUY_ID == 1)
                                  select new { r.M.ID, r.S.DESCRIPTION }).ToList();
    

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 31, 2013 7:27 AM
  • Thanks for the reply. However I'm still missing the

    X.REPORTDATE > Y.BEGIN_DATE and
    X.REPORTDATE < Y.END_DATE

    part but i guess we're half way. Can i put query one as an extension class/method or property? Or maybe override the property like below?

    public global vector<SalesGuy> SalesGuy
    {

    get

    {

       using( var context = from Entities)
       {
           var result = from s in context.SALESGUY
                              where REPORTDATE > s.DT_BEGIN &&

                                         REPORTDATE < s.DT_BEGIN 
                              select s;

           return result 

       }

    }

    }

    I'm using the entity framework primarily to read records. I dont have to put them back.



    • Edited by chimeister Thursday, January 31, 2013 11:13 AM
    Thursday, January 31, 2013 11:11 AM
  • Hi,

    I think you can do X.REPORTDATE > Y.BEGIN_DATE and X.REPORTDATE < Y.END_DATE in the where clause of query2 Alex mentioned:

    r.M.REPORTDATE > r.S.BEGIN_DATE and r.M.REPORTDATE < r.S.END_DATE

    In addition, I think you can try dynamic linq or expression tree to store query:

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    http://msdn.microsoft.com/en-us/library/bb882637.aspx

    Tuesday, February 5, 2013 8:31 AM
  • Did you ever get an answer to your question?
    Friday, March 8, 2013 1:33 PM
  • No i didnt.
    Friday, March 8, 2013 3:12 PM