CODEWITHSUNDEEP
c#sqlentity-frameworklinq
waffles
waffles

Reputation: 1396

Find all parents in hierarchical SQL database

Using ASP.NET 4.5 and EF 6, I've put together a multilevel data model that looks like this:

Organization, which has an ICollection of:

_____Workspaces, which has an ICollection of:

__________Projects, which has an ICollection of:

_______________Cards

If I've got a Card Id, how do I find the parents above it? I can't even figure out how to get one level of parent. To find the Project that is the parent of a card with Id of myCardId, I want to write something like:

var project = db.Projects.Where(p => p.Cards.Where(c => c.Id == myCardId));

Any guidance? Good tutorials you can point me to?

Update: Here's my Project model:

public class Project
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public DateTime Created { get; set; }

    public virtual ICollection<Card> Cards { get; set; }   
}

And here's my Card:

public class Card
{
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime Created { get; set; }
    public string Notes { get; set; }          
}

When I look at the tables in the database, the Card has a Project_Id column, but that happened magically from something Entity Framework did - I believe. Because the Card model has no Parent object on it, I can't programatically ask for things like Card.Project.Workspace.Organization...

Did I set up the whole thing backward?

Upvotes: 1

Views: 305

Answers (3)

siva
siva

Reputation: 525

Your Model should be like this:

public class Project
{
  public int projectId { get; set; }
  public int workspaceId { get; set; }
  public string Title { get; set; }
  public string Description { get; set; }
  public DateTime Created { get; set; }
  public virtual ICollection<Card> Cards { get; set; }   
}

public class Card
{
  public int cardId { get; set; }
  public int projectId { get; set; }
  public string Title { get; set; }
  public DateTime Created { get; set; }
  public string Notes { get; set; }          
}

Your Card table should have reference to projectid, and project table should have reference to workspaceid, and workspace table should have reference to organisationid.

Then you can use karthikb2win's query to get Organization or any parent tables.

Upvotes: 1

karthikkumar subramaniam
karthikkumar subramaniam

Reputation: 448

Hope you have references for each tables.

so you can get the organization by.

var organization = (from o in Organization
                    from w in Workspaces
                    from p in Projects
                    from c in Cards
                    where c.Id == myCardId && p.projectid == c.projectid && w.workspaceid == p.workspaceid && o.organizationid == w.organizationid select o).FirstOrDefault();

if its different from your code then post your collection structure here, so that anyone can help.

Upvotes: 2

Totodile
Totodile

Reputation: 150

I imagine that find the parents means go up in the Hierarchy so you should be able to write somethin like 'var project = db.Projects.Where(p => p.Cards.Where(c => c.Id == myCardId));'

var t= db.Cards.where(x=> x.id==myCardId).Select(y=> y.Project.Workspace.Organization);

//this should return the organization

Upvotes: 1

Related Questions