当前位置: 动力学知识库 > 问答 > 编程问答 >

Change order to all records linq to Sql c#

问题描述:

I have three tables.

In first table I have articleId, articleName ..., in second table storeId, storeName, storeLocation, maxNumOfArticles... in third table articleId, storeId and order.

I want for example in first store to add new article. what

I need to do is to increment the order to all articles in the store, so the new article would have order 1.

For example in store A maxNumOfArticles = 5, when I insert new article, article with num 5 will be deleted , article with order 4, now will be with order 5, article with order 3 will have order 4..... and the new article will have order 1.

网友答案:

That's not something LINQ to SQL is meant to do. First, you need a view that automatically numbers your articles by store:

CREATE VIEW ArticleStoreView AS
SELECT a.articleId ,
       a.articleName ,
       a.publicationDate,
       asm.storeId ,
       s.storeName ,
       s.maxNumOfArticles,
       ROW_NUMBER() OVER ( PARTITION BY asm.storeId ORDER BY publicationDate DESC  ) AS [Order]
        FROM dbo.Article a INNER JOIN dbo.ArticleStoreMap asm  ON asm.articleId = a.articleId
INNER JOIN dbo.Store s ON s.storeId = asm.storeId 

This will ensure that your order is always correct.


To get just the relevant articles, you can write this query:

SELECT  asv.articleId ,
        asv.articleName ,
        asv.publicationDate ,
        asv.storeId ,
        asv.storeName ,
        asv.maxNumOfArticles ,
        asv.[Order]
FROM    dbo.ArticleStoreView asv
WHERE   asv.[Order] <= asv.maxNumOfArticles
ORDER BY asv.publicationDate DESC

To delete the old article/store maps:

DELETE  dbo.ArticleStoreMap
FROM    dbo.ArticleStoreMap asm
        INNER JOIN dbo.ArticleStoreView asv ON asv.articleId = asm.articleId
                                               AND asv.storeId = asm.storeId
WHERE   asv.[Order] > asv.maxNumOfArticles 

To delete the old articles that are no longer mapped

DELETE dbo.Article
FROM dbo.Article a WHERE NOT EXISTS (SELECT * FROM dbo.ArticleStoreMap asm WHERE asm.articleId = a.articleId)
网友答案:

If you want to read Top 5 ordered in descending take look at this code :

myList.OrderByDescending(x => x.InsertDate).Take(5);
分享给朋友:
您可能感兴趣的文章:
随机阅读: