SQLite and models with LINQ

Hi Experts,

Let's say I have a player class (model).  I can save the player class easily with SQL and LINQ with just an ID and a text member that maps right to the table.  (I can specify the [PrimaryKey, AutoIncrement] attributes, etc).

Let's say I have a playerChallenge class - where for each challenge, there are a list of obstacles.  I guess the best thing is to have a row with challengeID, obstacleID, for each obstacle in that challenge (repeating challengeIDs).

My question is, how do I create the model for this class so that it's best used with LINQ?  Normally I'd write a model that contains 2 members - an ID and a List<int> for obstacleIDs.  But it seems like I'm doing something wrong because I won't get clean support for filling up that list without doing it manually (which is fine - just wondering if there's a cleaner way)...

Thank you!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Craig WagnerSoftware ArchitectCommented:
It sounds like you want a many-to-many relationship (a challenge can have many obstacles and an obstacle can be in many challenges).

Create two classes, Challenge and Obstacle. In each class create a list of the other. IOW, Challenge contains a list of Obstacles and Obstacle contains a list of Challenges. When EF generates the model it will create a join table to resolve the many-to-many relationship but from the code (logical) perspective it will simply look like a Challenge has Obstacles and an Obstacle belongs to Challenges.

public class Challenge
    public int ChallengeId {get; set;}

    // other properties about a challenge

    public virtual ICollection<Obstacle> Obstacles {get; set;}

public class Obstacle
    public int ObstacleId {get; set;}

    // other properties about an obstacle

    public virtual ICollection<Challenge> Challenges {get;set;}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
threadyAuthor Commented:
Thanks for your response.  Would I need to use the sqlite extensions for this to work?   If not, this is something new to me.  I just discovered the sqlite extensions last night and would have to decide between the two methods.

Craig WagnerSoftware ArchitectCommented:
I'm not familiar with sqlite extensions so I'm afraid I can't answer that.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

threadyAuthor Commented:
I'll get to verify this evening.  Thanks again
threadyAuthor Commented:
When we use this ICollection approach, will it always populate on a query, or is it possible to do "shallow" queries on the parent object only?
threadyAuthor Commented:
Hi again Craig,

I'm finally coming back to this, sorry for the wait.  I think I need to reiterate my question with a more specific example to understand ORM better with lists.  Let's say I have this object:

    public class Player
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        public string Name { get; set; }

        public List<int> SpecificIds { get; set; }

Open in new window

Is there a way to deal only with one table in ORM, so that I can get a list of SpecificIds from my query?  If yes, great, but I'm wondering if I'm going about this the wrong way...

Thanks again,
threadyAuthor Commented:
Yep, I think I'm definitely complicating my life for nothing.  I think my best bet is to just create my own join tables since I will be doing lots of different kinds of queries in my app involving specifics where I don't want all the columns from each table all the time...  Still interested in your input of course!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.