public int GetCount(Expression <Func <T, bool> > predicate)
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Set <T>().Count(predicate));
}
}
public Invoice GetByCountry(string country)
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Invoices.First(x => x.BillingCountry == country));
}
}
protected static ChinookEntities CreateContext()
{
ChinookEntities context = new ChinookEntities();
context.Database.Log = PrintToConsoleWindow;
return(context);
}
public List <Album> Search2(string title, int?artistId)
{
using (ChinookEntities context = DbContextFactory.Create <ChinookEntities>())
{
var query = from x in context.Albums
select new {
Album = x,
ArtistName = x.Artist.Name
};
if (title.HasValue())
{
//if (string.IsNullOrEmpty(title) == false)
query = query.Where(x => x.Album.Title.Contains(title));
}
if (artistId.HasValue)
{
query = query.Where(x => x.Album.ArtistId == artistId.Value);
}
var list = query.ToList();
foreach (var x in list)
{
x.Album.ArtistName = x.ArtistName;
}
return(list.ConvertAll(x => x.Album));
}
}
public List <Album> Search(string title, int?artistId)
{
using (ChinookEntities context = DbContextFactory.Create <ChinookEntities>())
{
var query = from x in context.Albums
select x;
if (title.HasValue())
{
//if (string.IsNullOrEmpty(title) == false)
query = query.Where(x => x.Title.Contains(title));
}
if (artistId.HasValue)
{
query = query.Where(x => x.ArtistId == artistId.Value);
}
var albums = query.ToList();
Dictionary <int, string> artistNames = context.Artists.ToDictionary(x => x.ArtistId, x => x.Name);
foreach (var x in albums)
{
string artistName = artistNames[x.ArtistId];
x.ArtistName = artistName;
}
return(albums);
}
}
public Album GetByPK(int albumId)
{
using (ChinookEntities context = DbContextFactory.Create <ChinookEntities>())
{
return(context.Albums.FirstOrDefault(x => x.AlbumId == albumId));
}
}
public Track GetByPK(int trackId) //GetByPK와 DeleteByPK가 entity마다 형태가 동일하므로 상위 클래스인 EntityData로 올릴 수 있다고 생각할 수도 있지만, PK는 entity마다 달라질 수 있다. playlist만 해도 PK가 int 타입 2개 -> 상위클래스로 올릴 수 없다.
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Tracks.FirstOrDefault(x => x.TrackId == trackId));
}
}
public string ToLongText()
{
using (ChinookEntities context = new ChinookEntities())
{
List <T> list = context.Set <T>().ToList();
//string result = "";
//foreach (T entity in list) // 동작은 하겠지만 entity의 개수가 많으면 많을 수록 비효율적.
//{
// result += entity.ToString();
// result += ", ";
//}
StringBuilder builder = new StringBuilder();
foreach (T entity in list)
{
//builder.Append(entity.ToString());
builder.Append(entity.ToText());//부분 클래스가 아니라 ToText()를 정의한 경우 -> EntityData<T>의 T는 참조타입이어야 한다는 제약조건이 있는데 모든 참조타입이 ToText()라는 메서드를 가진 것이 아니므로 오류. where T:Class가 아닌 where T:Entity로 제약조건을 바꾸고 Entity에 ToText()를 선언한 후 Artist와 Album이 Entity를 상속받으면 됨.
builder.Append(", ");
}
return(builder.ToString());
}
}
public Invoice GetByPK(int invoiceId)
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Invoices.FirstOrDefault(x => x.InvoiceId == invoiceId));
}
}
/*
* public List<Album> Search(string title, int artistId)
* {
* ChinookEntities context = new ChinookEntities();
*
* var query = from x in context.Albums
* where x.Title.Contains(title) &&
* x.ArtistId == artistId
* select x;
*
* var albums = query.ToList();
*
* // Round Trip... 2번 발생
* // 위의 albums의 query와 Dictionary의 query 발생
* Dictionary<int, string> artistNames = context.Artists.ToDictionary(x => x.ArtistId, x => x.Name);
*
* foreach (Album album in albums)
* {
* album.ArtistName = artistNames[album.ArtistId]; // logN
* }
*
* //List<Artist> artists = context.Artists.ToList();
*
* //foreach (Album album in albums)
* //{
* // // Find() 함수는 순차 정렬하여 처리하기 때문에 데이터가 많을 경우 비효율적이다...
* // Artist artist = artists.Find(x => x.ArtistId == album.ArtistId);
* // album.ArtistName = artist.Name;
* //}
*
* context.Dispose();
*
* return albums;
* }
*/
public List <Album> Search(string title, int artistId)
{
using (ChinookEntities context = DbContextFactory.Create())
{
context.Database.Log = x => Debug.WriteLine(x);
var query2 = from x in context.Tracks
select new { x.TrackId, x.Name };
var list2 = query2.ToList();
Dictionary <int, string> trackNames = context.Tracks.ToDictionary(x => x.TrackId, x => x.Name);
var query = from x in context.Albums
where x.Title.Contains(title) &&
x.ArtistId == artistId
select x;
var albums = query.ToList();
Dictionary <int, string> artistNames = context.Artists.ToDictionary(x => x.ArtistId, x => x.Name);
foreach (Album album in albums)
{
album.ArtistName = artistNames[album.ArtistId]; // logN
}
context.Dispose();
return(albums);
}
}
public Artist GetByPK(int artistId) //GetByPK랑 DeleteByPK는 매개변수가 각각 다르므로 EntityData로 못 올림.
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Artists.FirstOrDefault(x => x.ArtistId == artistId)); // First : 조건을 만족시키는 것이 없으면 예외를 던짐. FirstOrDefault : 조건을 만족시키는 것이 없으면 Default가 반환. Artist의 Default값은 null(Artist가 class이므로). x는 각 entity.
}
}
/// <summary>
/// 익명 타입 사용..
/// </summary>
/// <param name="title"></param>
/// <param name="artistId"></param>
/// <returns></returns>
public List <Album> Search2(string title, int artistId)
{
using (ChinookEntities context = DbContextFactory.Create())
{
var query = from x in context.Albums
where x.Title.Contains(title) &&
x.ArtistId == artistId
select new
{
Album = x,
ArtistName = x.Artist.Name
};
var list = query.ToList();
foreach (var item in list)
{
item.Album.ArtistName = item.ArtistName; // logN
}
context.Dispose();
// List에 담긴 데이터 전체 형식 변환하여 리턴하기
return(list.ConvertAll(x => x.Album));
}
}
public List <Album> Search(string albumTitle, int?artistId)
{
using (ChinookEntities context = new ChinookEntities())
{
Dictionary <int, string> artistNames = context.Artists.ToDictionary(x => x.ArtistId, x => x.Name);
IQueryable <Album> query = from x in context.Albums
select x;
// lazy execution
if (string.IsNullOrEmpty(albumTitle) == false)
{
query = query.Where(x => x.Title.Contains(albumTitle));
}
//if (artistId != null)
if (artistId.HasValue)
{
query = query.Where(x => x.ArtistId == artistId);
}
var albums = query.ToList();
foreach (var album in albums)
{
//album.ArtistName = artists.Find(x => x.ArtistId == album.ArtistId).Name;
album.ArtistName = artistNames[album.ArtistId];
}
return(albums);
}
}
public List <Album> Search(string albumTitle, int minArtistId, int maxArtistId)
{
using (ChinookEntities context = new ChinookEntities())
{
IQueryable <Album> query = from x in context.Albums
select x;
// lazy execution
if (albumTitle != null)
{
query = query.Where(x => x.Title.Contains(albumTitle));
}
if (minArtistId != 0)
{
query = query.Where(x => x.ArtistId >= minArtistId);
}
if (maxArtistId != 0)
{
query = query.Where(x => x.ArtistId <= maxArtistId);
}
return(query.ToList());
}
}
public int GetCount()
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Set <T>().Count());
}
}
public Artist GetByPK(int artistId)
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Artists.FirstOrDefault(x => x.ArtistId == artistId));
}
}
public int GetCount()
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Artists.Count());
}
}
public List <Invoice> Search(string country, string state, string city)
{
using (ChinookEntities context = new ChinookEntities())
{
var customersFirstName = context.Customers.ToDictionary(x => x.CustomerId, x => x.FirstName);
var customersLastName = context.Customers.ToDictionary(x => x.CustomerId, x => x.LastName);
var query = from x in context.Invoices
select x;
if (string.IsNullOrEmpty(country) == false)
{
query = query.Where(x => x.BillingCountry.Contains(country));
}
if (string.IsNullOrEmpty(state) == false)
{
query = query.Where(x => x.BillingState.Contains(state));
}
if (string.IsNullOrEmpty(city) == false)
{
query = query.Where(x => x.BillingCity.Contains(city));
}
var invoices = query.ToList();
foreach (var x in invoices)
{
x.CustomerFirstName = customersFirstName[x.CustomerId];
x.CustomerLastName = customersLastName[x.CustomerId];
}
return(invoices);
}
}
public Customer GetByPK(int customerId)
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Customers.FirstOrDefault(x => x.CustomerId == customerId));
}
}
public List <Album> Search(string albumTitle, int minArtistId, int maxArtistId)
{
using (ChinookEntities context = new ChinookEntities())
{
IQueryable <Album> query = from x in context.Albums
select x;
//lazy execution 지연된 실행
if (albumTitle != null)
{
query = query.Where(x => x.Title.Contains(albumTitle));
}
//if (string.IsNullOrEmpty(albumTitle) == false)
// query = query.Where(x => x.Title.Contains(albumTitle));
if (minArtistId != 0)
{
query = query.Where(x => x.ArtistId >= minArtistId);
}
if (maxArtistId != 0) //0이라면 where문이 안붙음
{
query = query.Where(x => x.ArtistId <= maxArtistId);
}
return(query.ToList());
}
}
public Track GetByPK(int trackId)
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Tracks.FirstOrDefault(x => x.TrackId == trackId));
}
}
public Album GetByPK(int albumId)
{
using (ChinookEntities context = new ChinookEntities())
{
return(context.Albums.FirstOrDefault(x => x.AlbumId == albumId));
}
}
protected ChinookEntities CreateContext()
{
ChinookEntities context = new ChinookEntities();
context.Configuration.ProxyCreationEnabled = false;
return(context);
}
public void Delete(T entity)
{
ChinookEntities context = CreateContext();
context.Entry(entity).State = EntityState.Deleted;
context.SaveChanges();
}
public static ChinookEntities Create()
{
ChinookEntities context = new ChinookEntities();
context.Database.Log = Write;
return(context);
}
public List <Track> Search2(string trackName, int?artistId, int?albumId, decimal?minUnitPrice, decimal?maxUnitPrice) //쿼리를 한 번에 날림.
{
using (ChinookEntities context = new ChinookEntities())
{
context.Database.Log = x => Debug.WriteLine(x); //
var query = from x in context.Tracks
select new
{
ArtistName = x.Album.Artist.Name,
GenreName = x.Genre.Name,
AlbumTitle = x.Album.Title,
MediaTypeName = x.MediaType.Name,
Track = x
};
//이 방식으로 쿼리를 한 번에 처리하면 JOIN문이 수행됨 -> JOIN문은 Track의 데이터가 1000개, Album이 100개일 때 둘을 조인하면 연산을 1000*100번 수행해야함. Join이 많아질 수록 급격히 느려짐.
if (trackName.IsNullOrEmpty() == false)
{
query = query.Where(x => x.Track.Name.Contains(trackName));
}
if (albumId.HasValue)
{
query = query.Where(x => x.Track.AlbumId == albumId.Value);
}
if (minUnitPrice.HasValue)
{
query = query.Where(x => x.Track.UnitPrice >= minUnitPrice);
}
if (maxUnitPrice.HasValue)
{
query = query.Where(x => x.Track.UnitPrice <= maxUnitPrice);
}
var list = query.ToList();
foreach (var x in list)
{
x.Track.ArtistName = x.ArtistName;
x.Track.AlbumTitle = x.AlbumTitle;
x.Track.GenreName = x.GenreName;
x.Track.MediaTypeName = x.MediaTypeName;
}
//
List <int> intList = new List <int> {
3, 5, 1, 2
};
List <string> stringList = intList.ConvertAll(x => x.ToString());
// "3" "5" "1" "2"
return(list.ConvertAll(x => x.Track)); // 아래와 같은 뜻(람다식)
//List<Track> tracks = new List<Track>();
//foreach (var x in list)
// tracks.Add(x.Track);
//return tracks;
}
}
public Artist GetByArtistId(int artistId)
{
using (ChinookEntities context = DbContextFactory.Create())
{
// 조건을 만족하는 첫번째 원소를 반환한다.
// OrDefault는 아무것도 없으면 기본값(null)을 반환한다.
return(context.Artists.FirstOrDefault(x => x.ArtistId == artistId));
}
}
public void Insert(T entity)
{
ChinookEntities context = CreateContext();
//context.Albums.Add(album)
context.Entry(entity).State = EntityState.Added;
context.SaveChanges();
}
public void Delete(T entity)
{
using (ChinookEntities context = new ChinookEntities())
{
context.Entry(entity).State = System.Data.Entity.EntityState.Deleted;
context.SaveChanges();
}
}
public void Update(T entity)
{
using (ChinookEntities context = new ChinookEntities())
{
context.Entry(entity).State = System.Data.Entity.EntityState.Modified; //context의.entity라는 Entry의. 상태에 = 변경된 상태값을 대입.
//todo : 예외 처리
context.SaveChanges();
}
}
