Avatar of locdang
locdang
 asked on

Help with Linq Logic

Hi everyone,

I am having trouble with linq when it comes to objects that have a List / Array of sub objects attached to them. I understand the basics of linq to insert, update select and delete but only in the sense of each table is inderpendant of the others.

I think what I want to achieve is best explained in a senario...

Lets say we have our root object a "Bookcase" the Bookcase object in c# is represented as:
public class Bookcase
{
     public Guid BookcaseID {get;set;}
     public String BookcaseName {get;set;}
     public List<Book> Books {get;set;}
     public List<DVD> DVDs{get;set;}
     public List<Magazine> Magazines {get;set;}
}

and a database table of:

FIELD NAME      |      DATATYPE
      
ID            |      UniqueIdentifier      
BookcaseName      |      nvarchar(max)



then we have our Book, DVD and Magazine classes:


public class Book
{
     public Guid BookID {get;set;}
     public String Title {get;set;}
}

--------------------------------------------------------
FIELD NAME      |      DATATYPE
      
ID            |      UniqueIdentifier      
Title            |      nvarchar(max)

________________________________________________________

public class DVD
{
     public Guid DVDID {get;set;}
     public String FilmTitle {get;set;}
     public String Rating {get;set;}
}

--------------------------------------------------------
FIELD NAME      |      DATATYPE
      
ID            |      UniqueIdentifier      
FilmTitle       |      nvarchar(max)
Rating            |      nvarchar(2)

________________________________________________________

public class Magazine
{
     public Guid MagazineID {get;set;}
     public int Issue {get;set;}
}

--------------------------------------------------------
FIELD NAME      |      DATATYPE
      
ID            |      UniqueIdentifier      
Issue            |      int



Now if i want my total bookcase's current contence I have to query Bookcase for the ID, then query each other table in a seperate linq statement for Books, DVDs and Magazines...


Is there a way that i can query only the bookcase and get an already filled Bookcase object? or do I then have to do what I am doing and run 3 more queries as well and build the Bookcase object myself?

Thanks,

Xavier.
.NET ProgrammingC#

Avatar of undefined
Last Comment
Fernando Soto

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
dungla

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
locdang

ASKER
Thanks ;)
Fernando Soto

Hi locdang;

To your question, "Now if i want my total bookcase's current contents I have to query Bookcase for the ID, then query each other table in a separate linq statement for Books, DVDs and Magazines...", Not true. If you build the database correctly you can have a query as shown in the code snippet and return all the information of that book case as shown in the output below. The database was designed as shown in the image.

ID = 1  Bookcase Name = Book Case 1
==== Books ====
      Book ID = 1  Title = VSTO 3.0
      Book ID = 2  Title = WCF
==== DVDs ====
      DVD ID = 1  Film Title = Action  Rating = X
      DVD ID = 2  Film Title = Love  Rating = X
==== Magazines ====
      Magazine ID = 1  Issue = 11
      Magazine ID = 2  Issue = 12
      
Fernando
// The Linq query to get all the information
var bookcase = from bc in db.Bookcases
               select bc;

// The code needed to print all the information
foreach (var bc in bookcase)
{
    Console.WriteLine("ID = {0}  Bookcase Name = {1}", bc.BookcaseID, bc.BookcaseName);
    Console.WriteLine("==== Books ====");
    foreach (var b in bc.Books)
    {
        Console.WriteLine("\tBook ID = {0}  Title = {1}", b.BookID, b.Title);
    }
    Console.WriteLine("==== DVDs ====");
    foreach (var d in bc.DVDs)
    {
        Console.WriteLine("\tDVD ID = {0}  Film Title = {1}  Rating = {2}", d.DVDID, d.FilmTitle, d.Rating);
    }
    Console.WriteLine("==== Magazines ====");
    foreach (var m in bc.Magazines)
    {
        Console.WriteLine("\tMagazine ID = {0}  Issue = {1}", m.MagazineID, m.Issue);
    }
}

Open in new window

ScreenShot003.png
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy