asked on

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!
Mike

ASKER CERTIFIED SOLUTION

Craig Wagner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thready

ASKER

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.

Mike

Craig Wagner

I'm not familiar with sqlite extensions so I'm afraid I can't answer that.

thready

ASKER

I'll get to verify this evening. Thanks again

thready

ASKER

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?

thready

ASKER

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; }

        [Unique]
        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,
Mike

thready

ASKER

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!