locked
Recursive Function in EF RRS feed

  • Question

  • User-1506490071 posted

    Hello!

    I would like to make this query in Linq is it posibble? How  can I use scalar valued funtion mmBvIsFather which return 1 if the  item is child else return 0. I would like to get all the lists from the child organizations so that I can make CheckboxList or SelectListBox from it. I would like to get Name of the organization + List name for the Text  and Id of the list for the Value.  I put the link to image. > https://www.dropbox.com/s/duik3v773ceyycc/FunctionEF.jpg?dl=0

    1.ROOTOrganization

      2.Organization has 2. lists: workers and directos. When I make listbox I need the name if the organization + list name
         Organization(workers)
         Organization(directors)

      3.AnotherOrganization
         AnotherOrganization(workers)
         AnotherOrganization(buyer)

    With this approch I can't get to the value of the navigation property but only with joining rows in the select query.

    IEnumerable<SelectListItem> dropDownList= db.Database.SqlQuery<Lista>(@"SELECT DISTINCT lst.list_id,('(' + org.org_naziv +') '+ lst.list_naziv)  as list_naziv,lst.list_javen,lst.org_id, org.org_id 
    FROM Organizacija org JOIN Lista lst on org.org_id = lst.org_id JOIN Uporabnik up on up.org_id = org.org_id WHERE [dbo].[mmBvIsFather](org.org_id, @p0,@p1)=1 ORDER BY org.org_id asc" , orgID,globina,userID ).ToSelectList(d => d.list_naziv, d => d.list_id.ToString(), null);

    I would like to see if is possible to get orgnization name from navigation property in the Lista Entity : 

    ToSelectList(d => d.list_naziv + d.Organization.Org_naziv, d => d.list_id.ToString(), null); // get nullReferenceExc for the Organization navigation property. I tried with Include("Organizacija") but the same error raise.

    With this approach I dont know  how to use mmBvIsFather  function in where clause:

    var query = from lst in db.Lista
                            join upo in db.Uporabnik on lst.org_id equals upo.org_id
                            join  org in db.Organizacija on  upo.org_id  equals org.org_id 
                            where mmBvIsFather(org.org_id, @p0,@p1)=1 //I would like to do something like this.
    select new SelectListItem { Text = lst.list_naziv + " (" + org.org_naziv + "),
    Value =lst.list_id" } "

    Tnx.

    Saturday, May 2, 2015 6:21 AM

All replies

  • User-271186128 posted

    Hi lozo,

    With this approch I can't get to the value of the navigation property but only with joining rows in the select query.

    IEnumerable<SelectListItem> dropDownList= db.Database.SqlQuery<Lista>(@"SELECT DISTINCT lst.list_id,('(' + org.org_naziv +') '+ lst.list_naziv)  as list_naziv,lst.list_javen,lst.org_id, org.org_id 
                                                                                    FROM Organizacija org
                                                                                    JOIN Lista lst on org.org_id = lst.org_id
                                                                                    JOIN Uporabnik up on up.org_id = org.org_id
                                                                                    WHERE [dbo].[mmBvIsFather](org.org_id, @p0,@p1)=1
                                                                                    ORDER BY org.org_id asc"
                                                                                    , orgID,globina,userID ).ToSelectList(d => d.list_naziv, d => d.list_id.ToString(), null);

    I would like to see if is possible to get orgnization name from navigation property in the Lista Entity : 

    ToSelectList(d => d.list_naziv + d.Organization.Org_naziv, d => d.list_id.ToString(), null); // get nullReferenceExc for the Organization navigation property. I tried with Include("Organizacija") but the same error raise.

    As for this issue, you could try to modify you code as below:

                Database.SqlQuery<Lista>(@"SELECT DISTINCT lst.list_id,('(' + org.org_naziv +') '+ lst.list_naziv)  as list_naziv,lst.list_javen,lst.org_id, org.org_id, org.org_naziv as org_naziv
                                                                                    FROM Organizacija org
                                                                                    JOIN Lista lst on org.org_id = lst.org_id
                                                                                    JOIN Uporabnik up on up.org_id = org.org_id
                                                                                    WHERE [dbo].[mmBvIsFather](org.org_id, @p0,@p1)=1
                                                                                    ORDER BY org.org_id asc"
                                                                                    , orgID, globina, userID).ToSelectList(d => d.list_naziv, d => org_naziv, d => d.list_id.ToString(), null);

    With this approach I dont know  how to use mmBvIsFather  function in where clause:

    var query = from lst in db.Lista
                            join upo in db.Uporabnik on lst.org_id equals upo.org_id
                            join  org in db.Organizacija on  upo.org_id  equals org.org_id 
                            where mmBvIsFather(org.org_id, @p0,@p1)=1 //I would like to do something like this.
                            select new SelectListItem { Text = lst.list_naziv + " (" + org.org_naziv + "),
                                                        Value =lst.list_id" } "

    As for this issue, you could try to modify you code as below:

    var query = from lst in db.Lista
                            join upo in db.Uporabnik on lst.org_id equals upo.org_id
                            join  org in db.Organizacija on  upo.org_id  equals org.org_id 
                            where db.mmBvIsFather(org.org_id, @p0,@p1)=1 //I would like to do something like this.
                            select new SelectListItem { Text = lst.list_naziv + " (" + org.org_naziv + "),
                                                        Value =lst.list_id" } "

    From your code and description, I suppose you want to manage the retrieval and display of hierarchical data. If that is the case, please refer to this article:

    http://www.mikesdotnetting.com/article/255/entity-framework-6-recipe-hierarchical-data-management

    Best Regards,
    Dillion

    Monday, May 4, 2015 3:22 AM
  • User-1506490071 posted

    Tnx for the link it is very useful.  

    where db.mmBvIsFather(org.org_id, @p0,@p1)=1 

    This don't work because there is no function with this name to select. The function is imported and  visible in EDMX  model browser but I can't set the return type. I think the function  is disabled. I show this in picture in the fitst post. This is the function I want to use. It's only cheking if it has father and return 1 or 0.

    ALTER  FUNCTION [dbo].[mmBvIsFather](@blv_vrsta int,@oce int,@globina int)  RETURNS bit
    AS BEGIN
    	declare @ret int
    	declare @bv_oce int
    		
    	set @ret = 0
    
    	if @blv_vrsta = @oce
    		return 1
    	
    	if @globina = 0
    		return 0
    	
    	if @blv_vrsta is null
    		return 0
    	
    	select 	@bv_oce= org_oce from dbo.Organizacija
    		where  org_id  = @blv_vrsta
    	set @ret= dbo.mmBvIsFather(@bv_oce, @oce, @globina -1)
    
    	return @ret
    END
    
    
    

    Monday, May 4, 2015 8:13 AM