CODEWITHSUNDEEP
c#sqlitelinq-to-sql
Ivan Silkin
Ivan Silkin

Reputation: 485

SQLite: The right way to design a database and to access its data with an AUTOINCREMENT ID?

Let's suppose that I have a database containing Artists and their Albums.

CREATE TABLE Artist (
ArtistID INTEGER        PRIMARY KEY,
ArtistName TEXT NOT NULL
);


CREATE TABLE Album(
AlbumName TEXT PRIMARY KEY,
ArtistID INTEGER REFERENCES Artist(ArtistID),
Year INTEGER
);

So then I create an application inside of Visual Studio and connect the SQLite database to my project using sqlite/sql server compact toolbox, I then want to manage the database using C#

I create an application for my users. A user wants to find all the albums by a name of an artist.

If my primary key is an autoincrement property, do I have to use syntax like :

public static IQueryable<Artist> GetPossibleArtists(string name)
{
    var matches = from match in dB.Artists where match.ArtistName == name select match;

    return matches;
}

public static Artist GetFirstArtistIfExists(string artistName)
{
    var artistMatches = GetPossibleArtists(artistName);

    if (artistMatches.Count() == 0)
        return null;
    return artistMatches.First();
}

So that I first access the database to find the artist by its ID because I can't simply find albums by the artist's name, because the artist's name is not a primary key and I can't search the "Albums" table by the artist's name, I can only search this table by the artist's ID And then I can finally find all the albums by the artist's id

public static IQueryable<Album> GetPossibleAlbums(long artistID)
{
    var matches = from match in dB.Albums where 
                  match.ArtistID == artistID
                  select match;

    return matches;
}

The questions are : 1) What am I doing wrong here and is there a better way to access all the albums of an artist so that I do not need to access the database to "find the ID of an artist by its name" first before I manage to find all the albums by the artistID? 2) I could possibly design my database wrong, are there any suggestions? 3) Is it a good idea to store the artist's name inside of the "Album" table and how do I need to do this to keep my Artist's autoincrement primary key exist at the same time?

Upvotes: 1

Views: 188

Answers (1)

Steve
Steve

Reputation: 216353

You need a JOIN to associate the Albums table to the Artists table on the ArtistID field

var matches = from a in dB.Albums join b in db.Artists on a.ArtistID equals b.ArtistID
              where b.ArtistName == artistName
              select a;

join clause C# reference

Upvotes: 1

Related Questions